Using Current Directory In Save Name?
May 15, 2013
I am trying to use VB to save a workbook into the same directory but with a file name that references the folder it is stored in...if that makes sense!
Here is where I have got to so far but fails on save,
Sub Rename()
Application.DisplayAlerts = False
mdy = Month(Now()) & "." & Day(Now()) & "." & Year(Now())
fold = ThisWorkbook.Path
FName = fold & "-" & mdy & "-(New).xls"
SaveName = fold & "" & FName
ActiveWorkbook.SaveAs (SaveName)
Application.DisplayAlerts = True
End Sub
View 3 Replies
ADVERTISEMENT
Jun 10, 2009
I am trying to prompt the user for the directory to be saved in and file name to be saved as; then save the workbook in the input directory with the inputted file name.
View 3 Replies
View Related
May 28, 2014
This Macro (Located within "SWMS Auto-Fill.xlsm) scans the folder its in and transfers information into select cells within each .xlsm file it finds. However I need to move the SWMS Auto-Fill.xlsm file to the parent directory.
So I need it to scan: Workbook DirectorySection4
Code is below:
VB:
Sub Autofill_SWMSs()
Dim MyPath As String
Dim MyFile As String
Dim Wkb As Workbook
Dim Cnt As Long
[Code]....
View 1 Replies
View Related
Sep 25, 2008
What is the easiest way to display in a cell the opened file's directory path where the file is stored? I would like to use some sort of formula (instead of the VB route) but I don't know if such a thing exists.
View 5 Replies
View Related
Jan 10, 2012
I would like a macro that saves the current open workbook to a directory specified in a cell on the open work sheet.
View 4 Replies
View Related
Feb 2, 2010
The Kill function works OK but the ThisWorkbook.SaveAs part does not work for some reason.
Please note that there are 2-spaces between the words: P3 CATS
View 12 Replies
View Related
May 11, 2006
1. Is it possible to save additional worksheets (right now it is only copying one worksheet)?
2. How can I hard-code the directory path so the file goes to that one locatin (i.e. the "master directory")?
Also, is it possible to save userforms out of this workbook (i.e. some of the userforms contain information that is useful...it would be great to have them in the "output" file that gets saved to the "master directory"). Thanks!
Private Sub SAPfile_Click()
myfile = Application.GetSaveAsFilename(fileFilter:="Excel Files (*.xls), *.xls")
Worksheets("Output-SAP Plan").Copy
ActiveWorkbook.SaveAs myfile
ActiveWorkbook.Close
MsgBox "SAP file created."
End Sub
View 9 Replies
View Related
Mar 16, 2007
I have a workbook that will save as if certain conditions are met. The workbook saveas filename is determined by user input into a textbox. I would like to define the path but allow the filename to be the value of the textbox. I used the ChDir function to do this but it is not working. Here's what I have:
Private Sub CrmFrm1_Click()
ChDir "S:AccountingProbationTest Files"
If Len(TextBox1) <> 12 Then
MsgBox "Incorrect Case File Number"
FrmSave.TextBox1.SetFocus
Exit Sub
Else
ActiveWorkbook.SaveAs Filename:=FrmSave.TextBox1.Value
End If
Unload Me
End Sub
View 2 Replies
View Related
May 20, 2013
I have a button that saves my excel workbook to a network drive, but what I'd like to do is have it save the file to a directory named after the user, instead of all the users in the department saving to one folder. For instance, R:customerserviceweight reconstructionusername, where username a directory named after the user.
Code:
Private Sub Save_Click()Dim Path As String
Dim FileName1 As String
Dim FileName2 As String
FileName1 = Range("A2")
FileName2 = Range("A3")
ActiveWorkbook.SaveAs Filename:="R:CustomerServiceWeight Reconstruction" & FileName1 & " - " & FileName2 & " - " & Format(Date, "yyyymmdd") & ".xlsm", FileFormat:=52
End Sub
View 1 Replies
View Related
Nov 5, 2008
I have managed to get my workbook to save with a filename based on two textboxes. I am not sure, however, how to define the directory. I have tried adding a ("mydirectory") on the saveas line but it does not work.
This is my
Private Sub CommandButton2_Click()
Dim wb As Workbook
Set wb = ActiveWorkbook
Dim strText As String
strText = txtLMSRef.Text & "," & txtPartial.Text
wb.SaveAs Filename:=strText
wb.Close
Set wb = Nothing
End
End Sub
View 9 Replies
View Related
May 20, 2009
I need save my workbook as copy to another directory, ( C: ) with VBA code
View 9 Replies
View Related
Oct 2, 2007
I am looking to see if a directory exists, and if it does, to save a file in it, otherwise create it, then save the file. My IsFileThere function works fine on files, but does not detect directories. The len(Dir(Filename)) statement always returns a 0, with the Dir statement always simply populating with "". It works fine on filenames - so can anyone tell me why it does not pick up directories?
Shouldn't the Dir function on a directory name return "."? I have excerpted some of my code to include here: Global Const StoredJobsSubdirectory = "Jobs"
Dim AppPath As String: AppPath = ActiveWorkbook.Path
'other code in here - activeworkbook changed, hence previous line
If IsFileThere(AppPath & "" & StoredJobsSubdirectory) Then
ActiveWorkbook.SaveAs FileName:=AppPath & "" & StoredJobsSubdirectory & _
"" & NameString & ".xls"
Else
MkDir AppPath & "" & StoredJobsSubdirectory
ActiveWorkbook.SaveAs FileName:=AppPath & "" & StoredJobsSubdirectory & _
"" & NameString & ".xls"
End If............................
View 2 Replies
View Related
Jan 21, 2012
how, via VBA, to save a worksheet ("ForExport") from my workbook into a new file (type CSV). To provide additional difficulty (not by my choice), the users will all have their own directories on the network to save their files into. These directories will NOT be mapped drives (e.g., "C:") but rather will be UNC paths (\parentusername)
So, obviously the filename and location are both rather dynamic, depending who is using the excel template.
The workbook has three sheets:Base Form (sheet 1)ValidationResources (sheet 2, provides data for menus/validation)ForExport (sheet 3, contains data written to it from the Base Form)
The workbook has fields to capture UserName, FileName (without an extension), and FilePath.UserName = captured by VBA code from the users login informationFileName = concatenated field based on some other fieldsFilePath = concatenated field of a set parent path (\parent) plus the UserName (so, \parentusername)What I want to do is have a macro that takes the ForExport sheet, saves it as a CSV file (with the file name from the field holding that info) into the directory I've specified in the FilePath field.
Is what I'm trying to do even possible? I have a sample workbook I can provide if you need something to work with. Not sure if I can attach it here.
View 2 Replies
View Related
Oct 29, 2012
I have some code that copies a worksheet and saves into a new workbook and saves into a specified folder and renames the new workbook.
It is all working fine however it is saving to my desktop and not the specified directory. I know there is a lot similar out there and i have exhausted my google skills trying to find the similar format to the one i am after.
Code:
Sub MonthlyReset()
Dim sourceSheet As String
Dim clearR As Range
Dim newFile As String
[Code]....
View 2 Replies
View Related
Jun 17, 2014
I have recorded the below code that functions fine from my computer. My issue is that I need to export/import this macro to other workstations who may have the save to location mapped to a different drive letter. Is there a way to replace the drive letter with the URL (SharePoint)?
ChDir "E:3. CRQsRemedy Dumps"
ActiveWorkbook.SaveAs Filename:="E:3. CRQsRemedy DumpsToday_CRQ_8Dump.xls" _
, FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Save To Directory: \server_1Dir_1Dir_2FolderToday_CRQ_8Dump.xls
Permissions are in place.
View 1 Replies
View Related
Apr 1, 2014
I am using this code to generate a text to a directory, but I would like to know how to call a save as prompt if the directory doesn't exist?
[Code] ..........
If the folder macro does not exist, then it will prompt a message saying that the folder macro cannot be found, and the save as prompt will appear.
View 3 Replies
View Related
Mar 3, 2014
I have several hundred text files which I would like to reformat and save as excel without needing to open each file individually.
I'm entirely new to working with macros, so, I was able to use macro recorder to save the steps to reformat the file, but would like setting up the part of the macro to automatically open and reformat all of the files.
View 2 Replies
View Related
Sep 8, 2006
Read “My Documents” Path And Use Result
Problem:
Note:
Typical user OS will be Windows XP Pro / Win 2K
Excel version : 97 / 2002 / 2003
1. Corporate network security settings will only allow directory/subdirectory creation in the “My Documents” section of customers individual computers.
2. Per customer request, VBA application needs to save extracted files for future use.
3. I can specify an initial “My Documents” subdirectory be made and the VBA application file be loaded/copied into that location – i.e. – “My DocumentsCat”.
4. When VBA application is opened from that specified directory, (first time), the application needs to make an additional subdirectory tree to save future files. I can read the opened from location via VBA with the following:
Dim filepath As String
filepath = ThisWorkbook.Path
As an example – this code would produce a string definition of “filepath” – such as the following:............................
View 5 Replies
View Related
Feb 23, 2014
I've set up a series of linked spreadsheets that feed data to an overall sheet. I work in a school, and each day the staff fill in student data, which then updates back into my overview spreadsheet.
Now the term has finished, I want to be able to store the data, then wipe the spreadsheets so they can be used for the new term (entering student data each day).
(There are about 20 different files I want to save, and don't want to have to copy/paste values for each sheet!)
View 4 Replies
View Related
Apr 23, 2009
I'm running a macro that intially opens an input box where you specify the filepath and name of a .ped file to import into the macro. The default path of H:BOM2.ped may not always be the path I use. It may be just H:.ped or H:Bom1.ped.
HTML INITIAL CHECK
Response = MsgBox(".............do you want to import BOM data?", vbYesNo)
Do Until Response = vbNo
'SELECT RAW FILE
Dim Message, Title, Default, MyValue
Message = "Please Enter PathFile Name for Source File"
Title = "Create CHINA INSPECTION REPORT(s)" ' Set Title.
Default = "H:BOM2.ped" ' Set Default.......
View 9 Replies
View Related
Jun 2, 2009
I have a form button I am currently using to save the workbook. However due to the workbook being so large is taking a little longer than I want to save. Is there a way to change the macro to save the current sheet only?
Sub Save()
'
'
'
ActiveWorkbook.Save
End Sub
View 9 Replies
View Related
Feb 15, 2010
I want to schedule a print, for lets say every Sunday at midnight. I have managed to find some what of a solution by using the following vbscript and scheduling a print to default printer. Works perfectly if that is all I wanted.
Dim objExcel, objWorkbook, strFile
' Connect to Excel
Set objExcel = CreateObject("Excel.Application")
strFile = "C: est.xls"
' Open the Excel File for Printing
Set objWorkbook = objExcel.Workbooks.Open _
(strFile)
' Print to Default Printer
Set objSheet = objExcel. ActiveWorkbook.Worksheets(1)
objSheet.PrintOut
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
I do not want to print to an actual printer, therefore if I set my default printer as the document writer, the vbscript gets it as far as the "Save As" page. Is there a way I can add to this vbscript so it will save as well? Can I get it to save as "name"&[DATE], thereby having it save as a different name each time?
Can I change this vbscript to print to a named printer rather then a default printer? I am sure a PDF convertor may be of some assistance with this, but I do not have an PDF converter on my computer, and as this will be carried out on a computer that I will not have administration rights, I can not download one.
View 2 Replies
View Related
Nov 6, 2009
I have a spreadsheet that I send emails from. Currently I use the .PrintOut command just before .send and it prints the email to the default printer. Instead of printing to the printer i want to print the email to pdf or any file for archive purposes. I would also like the file to be located on a network drive with a file name from a cell in the spreadsheet with todays date. Is this possible?
Currently the code looks like this:
VB:
With OutMail
.to = sh.Range("A1").Value [code]....
View 9 Replies
View Related
May 30, 2012
I've been searching for code that will perform the following:
1. Save the current workbook then,
2. Save the current sheet as a tab delimited file to the desktop with the same name as the current workbook then,
3. Return to .xlsx file format
The file saved to the desktop can be overwritten each time the macro is run.
View 4 Replies
View Related
Nov 6, 2009
I have a spreadsheet that I send emails from. Currently I use the .PrintOut command just before .send and it prints the email to the default printer. Instead of printing to the printer i want to print the email to pdf or any file for archive purposes. I would also like the file to be located on a network drive with a file name from a cell in the spreadsheet with todays date. Is this possible?
With OutMail
.to = sh.Range("A1").Value
.CC = ""
.BCC = ""
.Subject = sh.Range("H1").Value & " " & sarBody
.HTMLBody = strbody & "<br><br>" & "<H3><B>Specific Notes, If any...</B></H3>" & vbCrLf & varBody & Signature
.PrintOut
.send
End With
View 9 Replies
View Related
May 4, 2006
I get an xl file sent to me every day which I upload into a database. I then save the file keeping its name and save it into a selected folder. Is it possible to add a macro at the beginning of my current macro that saves the file using its name to a selected folder?
View 4 Replies
View Related
Jun 22, 2007
I'm trying to save a file with current date and time...I used the code from the following thread:
Save File Name With Date/time Stamp
ActiveWorkbook.SaveAs Filename:="C:FinalOutput.xls " & _
format(Now(), "mm_dd_yyyy hh mm AMPM"), FileFormat:=xlNormal, Password:="", _
WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
Now when I try to run the macro, it gives me an error saying: Compile Error, Wrong number of arguments or invalid property assignment. And it highlights "format" in yellow.
View 9 Replies
View Related
Jul 14, 2014
Here is a macro I am using to save a workbook using 'Save As' .
I have it open a template workbook we use, and then perform a save as, pathed to a specific folder on the desktop. We do this every night just before midnight.
What I haven't been able to figure out is how to have it save to the next month folder (The folders with the name of all the months are already created) on the last day of each current month.
For example. In my code, the final folder in the path is the current month July. On July 31 when I run the macro, I would like it to save it in the folder called August.
Is there a way to code this so it will recognize what the current month is and then at the end of each current month save it in the next month?
[Code] .....
View 12 Replies
View Related
Feb 28, 2014
I want to take the current sheet and copy to a new workbook (keeping the tab name) and then save as the tab name (xlsl) and close.
I've found ones that do a Saveas box or ends up saving as "Sheet1," "Sheet2" etc but can't seem to find one that keeps tab name and saves.
Here is the one I was using, but it saves all tabs not just selected:
Code:
Sub CopySheet()
Dim fname As Variant, ws As Worksheet, wb As Workbook
Dim nCol As Integer, iCol As Integer
[Code].....
View 1 Replies
View Related
May 5, 2006
I am using a module to create and save a Word document from an Excel module. I'm using the following line to save the document which works fine but (and this is a big BUT)(to quote a TV programme from long ago) obviously everytime I save the document it will overwrite itself. Is there a way of adding the date it is saved on to the file name so it creates a new document each time.
objWordDoc.SaveAs Filename: = "WAGReport"
I have tried changing it like this but get an error message
Dim dtMyDate As Date
dtMyDate = Format(Date, "DDMMYY")
dtMyDate = Sheets("Client Master").Range("d2")
objWordDoc.SaveAs Filename: = "WAGReport" & dtMyDate
View 3 Replies
View Related
