Excel SaveAs from Access Module

[ossse]

From an Access Module I am trying to save an excel file under a new name. The problem is, I need to it to prompt the user for the file name and location.

I've tried using the '.GetSaveFileName' that I found in VBA help, but it doesn't seem to work in Access. Any help is greatly appreciated.

Here is my code

Sub alterTXT()

Dim dbs As Database
    Dim objXL As Object, objWB As Object
    DoEvents
    Set objXL = CreateObject("Excel.Application")
    Set dbs = CurrentDb()
    
With objXL
  .Visible = True
  Set objWB = .Workbooks.Open("C:\Local\Shared\FilterTemp.xls")
  
  .fileSaveName = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt")

End With
    
End Sub

Thanks

 

[PHV]

Use this instead:
objXL.GetSaveAsFilename

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ossse]

thank you, the '.GetSaveAsFilename' part works, however there is a syntax error with the rest of it, and I can't figure it out, here is what I have;

.GetSaveAsFilename (fileFilter:="Text Files (*.txt), *.txt")

 

[PHV]

dim varFile As Variant
varFile = .GetSaveAsFilename (fileFilter:="Text Files (*.txt), *.txt")
If varFile = False Then
MsgBox "Aborted !"
Else
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ossse]

Works great... now i've been trying to save it as a .txt file and it isn't liking it;

.saveAs fileName:=varFile, FileFormat:=xlTextMSDOS

It doesn't recognize the 'xlTextMSDOS'
When I use brackets, it gives me a syntax error

any ideas

 

[PHV]

Const xlTextMSDOS = 21

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ossse]

I'm getting the following error "object doesn't support this property or method".

I'm guessing that I probably entered something wrong, can you take a look... here is the entire code


Sub alterTXT()

Dim dbs As Database
Dim objXL As Object, objWB As Object
DoCmd.OutputTo acOutputTable, "Filtered", acFormatXLS, "C:\Local\Shared\FilterTemp.xls"
DoEvents
Set objXL = CreateObject("Excel.Application")
Set dbs = CurrentDb()

With objXL
.Visible = True
Set objWB = .Workbooks.Open("C:\Local\Shared\FilterTemp.xls")

.Range("A2", .Range("AD2").End(-4121)).Select
.Selection.Cut
.Range("A1").Select
.ActiveSheet.Paste
.Range("A1").Select

Dim varFile As Variant
varFile = .GetSaveAsFilename("Text File Name", fileFilter:="Text Files (*.txt), *.txt")

Const xlTextMSDOS = 21
.saveAs fileName:=varFile, FileFormat:=21

objWB.Close , , acSaveNo
.Quit
End With

End Sub

 

[PHV]

Const xlTextMSDOS = 21
objWB.SaveAs fileName:=varFile, FileFormat:=xlTextMSDOS

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ossse]

PH,
I owe you big time ... I havn't programmed in 5 years but had to write a program the last couple of weeks, and you've fixed all my problems ... and with such quick replies