Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving Excel File in Current Directory NOT My Documents
I am having two issue with the code below.
The Excel wookbook that executes this code is called JIBUpload-OKCity.xls. When code executes to the text file it changes the name of the sheet. The results I would like is that I would like the files to be save in the current directory not in My Documents. At the end of executing this Macro, I would like the user to be back where they stared with JIBUpload-OKCity.xls being the active book and any other workbooks created by this macro being closed. If the the file already exist, I want the macro to overwirte it without any messages to the user. Does anyone have any suggestion on how I can accomplished this or what I am doing wrong. Sub SaveTextFile() ' ' SaveTextFile Macro ' Dim AcctYear, AcctMth Application.ScreenUpdating = False ActiveWorkbook.Save Sheets("JIBUpload-OKCity").Select Range("AcctgPeriod").Select AcctMth = Str(Month(Range("AcctgPeriod").Value)) AcctYear = Str(Year(Range("AcctgPeriod").Value)) Sheets("JIBUpload-OKCity").Select ActiveWorkbook.SaveAs Filename:= _ "JIBUpload" & Trim(AcctMth) & Trim(AcctYear) & ".xls", _ FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False ActiveWorkbook.SaveAs Filename:= _ "JIBUpload-OKCity" & Trim(AcctMth) & Trim(AcctYear) & ".txt", _ FileFormat:=xlText, CreateBackup:=False Workbooks.Close Workbooks("JIBUpload-OKCity.xls").Open Application.ScreenUpdating = True Sheets("JIBUpload-OKCity").Select End Sub -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200611/1 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving Excel File in Current Directory NOT My Documents
To save in the current directory, include the path in the file name.
VBA Help Filename Optional Variant. A string that indicates the name of the file to be saved. You can include a full path; if you don't, Microsoft Excel saves the file in the current folder. "Fredriksson via OfficeKB.com" wrote: I am having two issue with the code below. The Excel wookbook that executes this code is called JIBUpload-OKCity.xls. When code executes to the text file it changes the name of the sheet. The results I would like is that I would like the files to be save in the current directory not in My Documents. At the end of executing this Macro, I would like the user to be back where they stared with JIBUpload-OKCity.xls being the active book and any other workbooks created by this macro being closed. If the the file already exist, I want the macro to overwirte it without any messages to the user. Does anyone have any suggestion on how I can accomplished this or what I am doing wrong. Sub SaveTextFile() ' ' SaveTextFile Macro ' Dim AcctYear, AcctMth Application.ScreenUpdating = False ActiveWorkbook.Save Sheets("JIBUpload-OKCity").Select Range("AcctgPeriod").Select AcctMth = Str(Month(Range("AcctgPeriod").Value)) AcctYear = Str(Year(Range("AcctgPeriod").Value)) Sheets("JIBUpload-OKCity").Select ActiveWorkbook.SaveAs Filename:= _ "JIBUpload" & Trim(AcctMth) & Trim(AcctYear) & ".xls", _ FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False ActiveWorkbook.SaveAs Filename:= _ "JIBUpload-OKCity" & Trim(AcctMth) & Trim(AcctYear) & ".txt", _ FileFormat:=xlText, CreateBackup:=False Workbooks.Close Workbooks("JIBUpload-OKCity.xls").Open Application.ScreenUpdating = True Sheets("JIBUpload-OKCity").Select End Sub -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200611/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change File Open default directory from My Documents | Setting up and Configuration of Excel | |||
Excel should let me sort the file directory when saving a file | Excel Discussion (Misc queries) | |||
Excel should open documents in the folder of the current file | Setting up and Configuration of Excel | |||
changing current directory to that of the current open file | Excel Programming | |||
Use Current directory to store file | Excel Programming |