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
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