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

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