Prompt to save as Macro

[Christine]

Hi group,

I feel that I am close to figuring this out, but am missing the last piece
of the puzzle. I have created a macro that when run, prompts the user to save
the file. The missing pieces are

1) I want the file to be saved in tab delimited format.
2) I want the file to be saved in the same place as the original file.

This is what I have so far:



Option Explicit

Sub SaveAsText()
'
' SaveAsText Macro

Dim FileName As String
FileName = "YourFileName"
Application.Dialogs(xlDialogSaveAs).Show (FileName)
End Sub




Thanks,
Christine

 

[JLGWhiz]

Option Explicit

Sub SaveAsText()
'
' SaveAsText Macro

Dim FileName As String
FileName = "YourFileName"
fName = Application.Dialogs(xlDialogSaveAs).Show (FileName)
ActiveWorkbook.SaveAs Filename:=fName
End Sub

 

[JLGWhiz]

P.S. Your old workbook will still be on file, so if you no longer need it,
you will have to delete it.

 

[Rick Rothstein]

Why are you popping up the dialog box? You know the file name already and
you can get the current path using this ThisWorkbook.Path. The SaveAs Method
has a file format argument named xlCurrentPlatformText which appears to save
the file Tab Delimited. A quick look online indicated that foreign (to me)
versions of Windows (for example, Chinese) might have problems with this
argument. So, you can try this and see if it works...

Sub SaveAsText()
'
' SaveAs Tab Delimited Text Macro
'
Dim FileName As String
FileName = "YourFileName"
With ThisWorkbook
.SaveAs .Path & "\" & FileName & ".txt", xlCurrentPlatformText
End With
End Sub

The SaveAs method has more options available to it, so you might want to
check it out in the help files.

 

[Christine]

Oh yes, I need the previous copy to be there. I'm just trying to take some
drudgery out of a repetitive, daily task

 

[Christine]

Hi JLG,

I tried the code you gave me, but it doesn't work. I get "Compile error:
Expected Function or variable" and it highlights the "fName =" part.

Have I done something wrong?

Best,
Christine

 

[Christine]

Hi Rick,

I'm not an expert at writing macros, so I couldn't tell you exactly why I'm
using that.

The code you gave me is very slick and works fast, but instead of saving it
to where the file is located on a shared drive, it is saving it to a folder
on my machine.

I appreciate the help though!

Christine

 

[Rick Rothstein]

I've never had to work with a shared drive. I thought the workbook path
would look back to the source, but apparently it copies the networked file
to a local drive and runs it from there. Do you know the path to the shared
drive? If so, you should be able to use that in place of where I have the
..Path reference. Just make sure you specify the path without the trailing
backslash (the code is already concatenating it in). If you have any trouble
implementing this, write back telling us exactly what the networked path is
and we'll see if we can doctor up the code for you.

 

[JLGWhiz]

Yeah the Option Explicit got me. Need to Dim the fName variable.

Dim fName As String

Put that after the sub title line.

 

[Christine]

Thanks JLG. I should have seen that myself, but it's been years since I did
any ASP!

 

[Christine]

Thank you Rick, I shall give that a try!

Best,
Christine