Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel SaveAs from Access Module

Status
Not open for further replies.

ossse

Programmer
Jun 26, 2007
49
US
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

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
 
Use this instead:
objXL.GetSaveAsFilename

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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")
 
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
 
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
 
Const xlTextMSDOS = 21

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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

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

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top