Saving Text File
 

Hi all,
I need some help with a macro that saves a worksheet as a text file when
called. My issue that I am currently having is getting the text file to save
in the same directory that the original excel spreadsheet was located instead
of the default location. I am admittedly a novice when it comes to VB so
please forgive me if I've made any glaring mistakes.

I've tried to use the curdir function, but without any real results. Again,
I want the "Export Sheet" to save as a text file in the same directory as the
original excel file. I'd like this to be a transparent operation for the
user so they don't "see" any of it happening...

Here is what my code looks like currently:

Sub save_as()


Application.ScreenUpdating = False
Application.DisplayAlerts = False

Sheets("ExportSheet").Select

Dim myPath As String
Dim myFile As String

myPath = curdir("J")
myFile = "import_file"
myFile = myFile & ".txt"

ActiveWorkbook.SaveAs Filename:=myPath & myFile, FileFormat:=xlText,
CreateBackup:=False

Sheets("Sheet1").Select
Sheets("import_file").Name = "ExportSheet"

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Thank you ahead of time for any and all suggestions!
--
cmungs

Saving Text File
 

something like:

myPath = activeworkbook.path & "\"
or
myPath = Thisworkbook.path & "\"

cmungs wrote:

Hi all,
I need some help with a macro that saves a worksheet as a text file when
called. My issue that I am currently having is getting the text file to save
in the same directory that the original excel spreadsheet was located instead
of the default location. I am admittedly a novice when it comes to VB so
please forgive me if I've made any glaring mistakes.

I've tried to use the curdir function, but without any real results. Again,
I want the "Export Sheet" to save as a text file in the same directory as the
original excel file. I'd like this to be a transparent operation for the
user so they don't "see" any of it happening...

Here is what my code looks like currently:

Sub save_as()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Sheets("ExportSheet").Select

Dim myPath As String
Dim myFile As String

myPath = curdir("J")
myFile = "import_file"
myFile = myFile & ".txt"

ActiveWorkbook.SaveAs Filename:=myPath & myFile, FileFormat:=xlText,
CreateBackup:=False

Sheets("Sheet1").Select
Sheets("import_file").Name = "ExportSheet"

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Thank you ahead of time for any and all suggestions!
--
cmungs

--

Dave Peterson