looking for some code to save to a destination
destination address is
C:Documents and SettingsstDesktopOJF
now the problem is OJF has folders named 1 to 500
so if cell d5 = 487 it will need to look in the above desination and then open the folder and save it there.
I am creating a document for work that automatically generates hyperlinks to pdf files in a given sub folder. Currently I have a section of code that opens a window to choose the folder location.
Code:
'Prompt user to select a directory
Do
Problem = False
Set ShellApp = CreateObject("Shell.Application"). _
Browseforfolder(0, "Please choose a folder", 0, "c:\")
On Error Resume Next
[Code] ........
What I actually want is to not have the window open, but excel automatically choose the sub folder location.
The directory looks like this
Z:ClientProjectDRAWINGS-2 RECORD COPIESPDF
The xlsm file is stored in the DRAWINGS folder, and I need it to point to the PDF folder.
if it's possible to use XlDialogOpen to open up a folder that you only have part of the name.
For example, say you have the following folder paths:
C:Main FolderSubfolder1Smith,Billy 2413
C:Main FolderSubfolder1Brown,Charlie Ray 2416
C:Main FolderSubfolder1Bunny,B 3619
C:Main FolderSubfolder1Fudd,E-G 1864
What I'm hoping to do is just look for the 4 digit number on the end finding that folder name and opening it up. The name conventions before the 4-digit serial number are quite random. Sometimes it includes their full first name, other times it's just an initial for the first name and other times there are other characters included in the name. The one thing that is constant is the 4 digit serial number on the end.
how I could get the Open dialog box to pop up and have the chosen folder number open?
I have alot of project folders on my harddrive.
All in format: I:/12345-costumer-projectname/
The five digits are unique for each project.
I make calculations for these projects using an excel file. In this excel I also type the projectnumber (cell J2)
Now i would like to make a button. When pressed, it checks the projectnumber cell J2, looksup the corresponding folder and saves the excelfile in PDF format in this folder.
I have found macro to find files in folders, but none which do the above.
I have up to 50 workbooks in one folder with data in a specific range. I also have one workbook which includes additional data, including conditional formatting and dropdowns. I need to copy the desired range from the first workbook in the source folder to the second workbook, then save the latter to my destination folder, using the same name as the first. I need to repeat this process for all workbooks in the source folder.
View 2 Replies View RelatedI have a list of items in an excel sheet... 500 of them... and there are images for them in an other folder... The problem is those images are in 1000's and I only need those 500 images.. Images are saved as "SKU.jpg eg NS2354.jpg and we have an SKU column in excel as well .. SKU and their images have same name...
Is there any solution to pick up only those 500 images from those thousands of images?
Is it possible to save those 500 images in an other folder?
I would like to create a code that will open all workbooks in folder "A" one at a time and then close the work book after another code is executed. So one workbook will open and while the workbook is open, I will call another code to format the active workbook and then the active workbook will be saved in folder "B" and closed and go to the next workbook in folder "A" to start all over. The folders are saved on the desktop.
View 5 Replies View RelatedI have a macro that copy one sheet of the Active workbook and sends it via email.
I need to add a code in this sheet so when one opens it from the email, with a command button to be able to save the file to specific, fixed folder on the local network with it’s original file name.
Looking for VBA code to automatically save an excel file as an .xlsx format, and saving to a specific folder: C:UserswolfmDesktopLoad Lists
I have been using the attached code, but it's not working. It saves to the desktop, not the folder. Also, when it creates the file name based on sFile = ActiveSheet.Range("C4").Value & ".xlsx", it adds wierd things to the file name. I want the file name to be simply the value in C4.
I have a macro that currently saves and prints the sheet I'm working on. At the moment, it saves everything in the same place and always calls the file the same name.
In the worksheet, a lookup does actually specify what folder it should be saved in and what name it should be given. How can I write the code so that it pulls up the name and the folder directly from the sheet it's saving?
Our small company only has 5 computers in it with mine acting as the "server". I want to put a macro on our customer order forms that all computers can access on the server that will save the file as 3 or 4 cell names serparated by commas and save it in a specific location on the server.
I got the macro to work perfectly on my computer but when I try to type in the appropriate path to the server the file gets saved in their My Documents folder 99% of the time. I did get it to work twice but it seembed to be completely random as it didn't work any of the times before or after.
Here's what I have so far: ...
I have the following code which saves a certain sheet to a new workbook and then emails it to a range of cells. The directory the file is saved in is from a cell, and changes depending on certain cells in the workbook. I would like to make it so if the folder it is saving to does not exist, it will create it. If this isn't possible with this set up, I would just like it to display an error message if the folder is not present.
VB:
Sub Save_Report()
Dim wbName As String
Sheets("TELELINK & ITINERARY FORM").Copy
[Code].....
I have a folder called "Report" at C: and inside this folder there are 12 folder named from 1 to 12 ("1","2",...,"12")
I need a macro to save my workbook as a file in C:Report(one of these folders depending on the month of a certain cell)
i.e. if the date in this cell is 8/12/2012 .. then save my file in C:Report12*.xlsm
This is the code i use
Code:
Private Sub CommandButton1_Click()
Dim fname As String
fname = Format$(Range("S8"), "dd-mm-yyyy")
On Error Resume Next
ActiveWorkbook.SaveAs Filename:="C:Reort" & fname & ".xlsm"
End Sub
So what i need now is what to insert after C:Report
I want to create a macro that will create a new folder called "Fungicide Quotes" under my documents and will save the workbook using the cell reference d4:f4 for the file name, which are merged cells. I have tried the following but can't get it to work. Any help would be appreciated, Thanks
Sub Save_wrkbk()
Dim strFilename, strDirname, strPathname, strDefpath As String
On Error Resume Next
strDirname = "Fungicide Quotes"
strFilename = Range("d4:f4").Value
strDefpath = "C:My Documents"
If IsEmpty(Filename) Then Exit Sub
MkDir strDefpath & strDirname
strPathname = strDefpath & strDirname & "" & strFilename.......................
how to save multiple file with other names in the same folder
View 1 Replies View RelatedI have this existing macro which saves each tab into a separate excel file. However, I'd like for it to also save them together in a new folder using the current month and date (named: QA Files May_05.06.14). The month and date would change according to current month/date. How would I incorporate that into this code? I am not good with macros.
Sub tabname()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
ws.Name = Range("D1").Value
Next
[Code]...
I have a report send to me daily. And I want to have a macro to save this report in the daily folder, such as “c:
eports8052008”, so tomorrow 's folder would be “c:eports8062008”.
All the daily folders already exist. Just need to change the file path. I tried some codes including sPath and format(now(), “mmddyyyy”), get error message.
I currently use the following code to create a duplicate file based on two cells within a directory and folder i specify. These cells consist of the team and week commencing date (mondays date of week which is cell 'Main Menu'!K8)
View 7 Replies View RelatedI need a macro in a workbook to look at all the files in the same folder that have "*att*.xls" in the name and determine and copy from the range A15:W515 only the rows that have data in at least columns A, C and D. Each file will vary as to how many rows there will be and there are more than the files with "*att*.xls" in the folder. The data will be on the only worksheet in each file and the worksheet is named "G2WAttendee_xls" the data from all the files need to be copied to the file called "Consolidated webinar reports.xls" (I am using Excel 2003) and to a sheet called "Attendance Data" and added to the end of the last paste.
At the start of the macro the current file "Consolidated webinar report.xls" should be saved to a sub folder of the current directory and have the date saved added to the name. The sub folder is called "Completed reports". The data in the original file on worksheet "Attendance Data" should be deleted.
At the end of the process all the files that have had data copied from them should be moved to the sub folder "Attendance reports consolidated" (This could be done as each file is closed if that is easier).
I have headings in row 1 of the "Attendance Data" worksheet that match the headings in the various files in the folder (which will always be in row 14 of the individual "*att*.xls" files).
The folder with all the files and the "Consolidated webinar report.xls" file is at path "Z:P and S MEvaluationsWebinar series 2012-13TB".
I have an Excel VBA Macro for creating/logging drawing numbers. Each drawing belongs to a job number. Each job number has a folder name containing the job number followed by a description (ie 999999 - bracket assembly) for storing drawing pdf's. The job number is only known as a 6 figure number in the drawing creation process BUT for the PDF saving process it is a string value...as my example above
I have to change my process by pre-creating the Job Number folder, then have the macro look for the appropriate folder by searching the parent folder for a sub folder containing the job number (always the first 6 figures).
I want to insert in my macro some code that searches...
The parent folder for a folder containing the job number. The macro value for the parent folder is P:engineeringdrawings (this never changes). the macro value for the job number is iOpenair (it's an entry that is entered as start the macro). Imagine the value for the job number is "999999"...so the search would be for "999999*.*" The code needs to search the parent folder, find the folder name that begins with a six digit number. Capture the complete name of the folder and store that name as a Dim value that I can call up elsewhere in my macro.
I have figured out how to save the workbook using a variable to saveas but I would like to create a new folder to place the new workbook into. I have tried a few things a am not having any luck. I recorded a macro and the code it produced is as follows.
View 6 Replies View RelatedI have an addin in a shared folder which was created by someone else. (it is a user form). I need to create a macro which will go to its existing folder and then save it in the users personal addin folder also picking up their user id. There will be about 100 users so easier this way than going round doing it for them.
So I suppose the query is three fold.
1, pick up the addin and save it in a personal addin folder.
2, Find the username as part of the path file. For instance my personal would be:
"C:Documents and SettingsO033116Application DataMicrosoftAddIns"
The "O033116" is where I will need a search completed to obtain the user id.
The addin is called ServiceCredt.xla
3, Activate the installed addin.
The reason I want it this way is so the user is unable to access any of the code and we are in control.
How do I write this to always save in a particular folder? C:Documents and SettingsErinDesktopJob Cost Estimates and how is it different if I am using a shared network?
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim varFileName As Variant
varFileName = Application.GetSaveAsFilename(Range("G6"), _
fileFilter:="Excel Files (*.xls), *.xls")
If varFileName False Then ThisWorkbook.SaveAs varFileName
End Sub
I am trying to save a file to a different directory but am not having any luck with the syntax.
so far have created new folders on the X drive. The x drive folders have been assigned a variable name which I am attempting to use again when telling the save as function where to save the file. the variable name is not being picked up in the new macro. I have declared the variable name as public at the start of my module.
My workbook has a worksheet for reports of sales by each sales person (which I had hoped to send by email). CDO didn't work because I have win7 and no flavors of Outlook and ShellExecute worked but truncated any sales beyond the first dozen. So.... My NEXT plan is to include a SAVEAS pdf for that report worksheet (with 12 or more reccords) using the salesperson's name and saving them all in a reports folder. Then I will go back and manually send those reports as attachments.
View 1 Replies View RelatedI'd like my macro to pop up the window that'd ask where I'd like my pdf file (exported from excel) to be saved. How can I do that? I understand that the code below will assign the name for the file and save it to desktop.
Code:
Sub SavePDF()
Dim MyFullName As String
MyFullName = "C:UsersqlarimerDesktopFilename.pdf"
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=MyFullName, _
Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End Sub
I am using this code to save all files in folder as CSV . I would like to add a letter to the beginning of file name for each file starting with a for file 1, b for file 2 etc.
Code:
strFile = Dir(mFolder & "*.xls*")
Do While strFile ""
Workbooks.Open mFolder & strFile
Range("D1").EntireColumn.Insert
[Code] .........
I have a workbook that is located in a folder and i need to copy this folder into multi locations, i need a save as macro that will save the workbook back to the same folder location it was opened from, i have got the workbook file name that i wish to use in N6 of the workshee
View 3 Replies View RelatedI have a file that I enter data into and I want to be able to save the file(s) in a folder according to the date. Can the following code be amended to save the files in a folder with the name being the date. Example:
If I enter data in today - when I clicked Save File As it would look for a folder named 2007-12-18 and if it wasn't there it would create one and save all files with todays date in that folder. If I use the file tomorrow it would look for a folder named 2007-12-19 and save all files with tomorrows date in that folder.
Option Explicit
Sub Button14_Click() 'variation of shg's code
Dim SaveAsFile
SaveAsFile = Application.GetSaveAsFilename _
(fileFilter:="Excel Files (*.xls), *.xls")
If SaveAsFile = False Then Exit Sub
If SaveAsFile = ThisWorkbook.FullName Then
MsgBox "Please select another path and file name.", vbCritical, "Save As Error"
Goto ErrorHandler
End If
On Error Goto ErrorHandler
ActiveWorkbook.SaveCopyAs (SaveAsFile).....................
I'm having a little trouble making this code work correctly. This macro, Once finished, is connected to a (Save) command button. Here is what is suppose to happen, First thing to remember is another sheet (Sheet1) has been populated with various information which is used as a receipt. The information came from sheet2. I need to copy sheet1 just as it is, format included, create a new empty sheet, paste the information into the new sheet, name the new sheet with the values from cells in sheet2, E3, D3, and the date formatted as the month and year only. Then I need to create a folder in the my documents folder named from the values from sheet2, E3, D3, and todays date with the current time. If the folder already exists, then I just need it to change to that folder, and save the newly created worksheet (as named above) into that folder. After the save is executed, I need to delete that new sheet from the current workbook. Then I need to return to sheet2 to the last active cell and run the last few lines of code which collects the value of the cell 1 to the right of the active cell and transfers it to the cell 1 to the left of the active cell.
VB:
Public Sub Button11_Click()
Dim wShell, fso, strFldr, xdir As String
Dim newbal As String
[Code].....