Issue With Save As

J

Jack

Hi,
I am trying to export an access table to an excel workbook using vba. At the
end I would like to prompt the user with a save as dialog box so that the
user can choose the corresponding path where the excel spreadsheet will be
saved. However I do
not know how to prompt the user with a save as dialog prompt. I appreciate
any help. Thanks.
CODE:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add

'Delete the default worksheets leaving only one, which we grab
On Error Resume Next
objExcel.DisplayAlerts = False
For Each objWorksheet In objWorkbook.Worksheets
objWorksheet.Delete
Next objWorksheet
objExcel.DisplayAlerts = False
On Error GoTo 0
Set objWorksheet = objWorkbook.Worksheets(1)
'END Delete the default worksheets leaving only one, which we grab

Do
'Check if a new worksheet needs to be added and rename
If objWorksheet Is Nothing Then
Set objWorksheet = objWorkbook.Worksheets.Add
End If
lngSheets = lngSheets + 1
objWorksheet.Name = strcDataSheetPrefix & Format$(lngSheets, "00")
'END Check if a new worksheet needs to be added and rename

'Write the field names and determine where to output data & _
and how many rows.

For lngCounter = 0 To rstData.Fields.Count - 1
objWorksheet.Cells(1, lngCounter + 1) =
rstData.Fields(lngCounter).Name
Next lngCounter
Set objRange = objWorksheet.Range("A2")

'END Write the field names ...

'Output the data, CopyFromRecordset moves the cursor in rstData after copy
objRange.CopyFromRecordset rstData, lngRows
DoEvents
'END Output the data...

'This will tell the loop to create a new worksheet
Set objWorksheet = Nothing
Loop Until rstData.EOF


rstData.Close
Set rstData = Nothing
Set objRange = Nothing
objWorkbook.SaveAs "C:\SFDB\bak\_0__AccessExcel\export.xls"
objWorkbook.Close
Set objWorkbook = Nothing
objExcel.Quit
Set objExcel = Nothing
 
P

p45cal

Jack;435311 said:
Hi,
I am trying to export an access table to an excel workbook using vba.
At the
end I would like to prompt the user with a save as dialog box so that
the
user can choose the corresponding path where the excel spreadsheet will
be
saved. However I do
not know how to prompt the user with a save as dialog prompt. I
appreciate
any help. Thanks.
CODE:

Check out *GetSaveAsFilename *method in Excel help
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top