Save as dialog box initial directory

[gauntletxg]

I'm trying to write a macro that will open a Save As dialog box in a specific directory with a specific file type. This is what I have so far:

Sub SaveAsTab()
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Application.Dialogs(xlDialogSaveAs).Show ("C:\My Text Files\"), xlTextWindows
End Sub

If you create a new workbook and run this macro, it works perfectly. What I'm trying to do is import a table from Access (using Office links > analyze with Excel). When you run the macro on a workbook that was created in this fashion, the initial directory is changed to My Documents. Any ideas on how to get around this?

Thanks.

 

[Rofeu]

Take a look at the the ChDir and ChDrive statements.

Cheers,

Roel

 

[gauntletxg]

That doesn't work, apparently this is a known bug in Excel. I was able to modify my code to get it to work though:

myFullName = Application.GetSaveAsFilename("C:\My Text Files\", Title:="Chose desired location", FileFilter:="Text (Tab delimited) (*.txt),*.txt")
ChDir "C:\"
If myFullName <> "False" Then ActiveWorkbook.SaveAs Filename:=myFullName, FileFormat:=xlTextWindows