Have an app where data is exported from Access to Excel via VB6. The transfer runs smoothly. However, if the user does not want to complete the transaction, I got problems.
Dim objExcelApp As Excel.Application
Dim xlsExcelSheet As Excel.Worksheet
objExcelApp.Workbooks.Add 'add the worksheet
Set objExcelApp = New Excel.Application
objExcelApp.Visible = True 'Did this as a reminder to save the worksheet. Else the program hangs at the end of the sub.
Next I open the Access connection then plant the info into Excel
If Dir$(App.Path & "book1.xls") <> vbNullString Then Kill App.Path & "book1.xls"
conn.Execute "SELECT * INTO [Excel 8.0; Database=" & App.Path & "book1.xls].[Sheet1]" & _
" FROM Members", iNum 'inum is to pick # of records transferred
After looping through the records, save the file and quit the application:
objExcelApp.DefaultFilePath = App.Path
objExcelApp.Application.SaveWorkspace (App.Path & "\book1")
objExcelApp.Quit
At the point of saving the file as book1.xls, if the user clicks cancel instead of save, I get an error msg "Method 'SaveWorkspace' of object_Application failed". Error number 1004. I work around this with a boolean. If any error occurs, boolean is false, no records transferred and an msgbox tells the user.
If Err.Number = 1004 Then 'user cancelled operation or any error
objExcelApp.Visible = False
objExcelApp.Quit 'This causes a msgbox 'Do you want to save ....'
bFileSaved = False
Exit Sub
End If
This seems to be a clumsy and obviously wrong way to accomplish the transfer thing. Is there a way to do it silently with excel not being visible and at the same time if the user clicks cancel there is no save file msg. Also, how to close the application at the end of the sub?
Thanks.
Kim
Dim objExcelApp As Excel.Application
Dim xlsExcelSheet As Excel.Worksheet
objExcelApp.Workbooks.Add 'add the worksheet
Set objExcelApp = New Excel.Application
objExcelApp.Visible = True 'Did this as a reminder to save the worksheet. Else the program hangs at the end of the sub.
Next I open the Access connection then plant the info into Excel
If Dir$(App.Path & "book1.xls") <> vbNullString Then Kill App.Path & "book1.xls"
conn.Execute "SELECT * INTO [Excel 8.0; Database=" & App.Path & "book1.xls].[Sheet1]" & _
" FROM Members", iNum 'inum is to pick # of records transferred
After looping through the records, save the file and quit the application:
objExcelApp.DefaultFilePath = App.Path
objExcelApp.Application.SaveWorkspace (App.Path & "\book1")
objExcelApp.Quit
At the point of saving the file as book1.xls, if the user clicks cancel instead of save, I get an error msg "Method 'SaveWorkspace' of object_Application failed". Error number 1004. I work around this with a boolean. If any error occurs, boolean is false, no records transferred and an msgbox tells the user.
If Err.Number = 1004 Then 'user cancelled operation or any error
objExcelApp.Visible = False
objExcelApp.Quit 'This causes a msgbox 'Do you want to save ....'
bFileSaved = False
Exit Sub
End If
This seems to be a clumsy and obviously wrong way to accomplish the transfer thing. Is there a way to do it silently with excel not being visible and at the same time if the user clicks cancel there is no save file msg. Also, how to close the application at the end of the sub?
Thanks.
Kim