Frankie_The_Flyer
Registered User.
- Local time
- Today, 09:28
- Joined
- Aug 16, 2013
- Messages
- 39
Still new to VB / VBA and trying to create a code that will export data from a query into Excel. I managed to get that far, but the process ended with a "Type Mismatch" message and the data not always transferring, (it should overwrite the previous export)
Looking at the Excel spreadsheet there are a couple of columns that are not in the same format as they are in the Database, which I thought may be bringing up the Mismatch warning.
Column A:A "Enquiry Number" is text in the db (because it may be a text / number mix) but is a number in Excel; $K column (G:G) comes out as "General"; Colun H:H with Meeting Date information is dd/mm/yyyy, but needs to be dd-mmm-yy hh:mm
I've tried to build a code that formats the spreadsheet after the information has been transferred, but now get a "Object Required" warning and no data transfer.
Any ideas where this is going wrong please?? (A full code to do it would be GREAT!!!)
Many thanks
Looking at the Excel spreadsheet there are a couple of columns that are not in the same format as they are in the Database, which I thought may be bringing up the Mismatch warning.
Column A:A "Enquiry Number" is text in the db (because it may be a text / number mix) but is a number in Excel; $K column (G:G) comes out as "General"; Colun H:H with Meeting Date information is dd/mm/yyyy, but needs to be dd-mmm-yy hh:mm
I've tried to build a code that formats the spreadsheet after the information has been transferred, but now get a "Object Required" warning and no data transfer.
Any ideas where this is going wrong please?? (A full code to do it would be GREAT!!!)
Code:
Private Sub ExportandFormatinExcel_Click()
On Error GoTo ExportandFormatinExcel_Click_Err
DoCmd.OpenQuery "Qry_Meeting_Export"
DoCmd.OutputTo acOutputQuery, "Qry_Meeting_Export", "Excel97-Excel2003Workbook(*.xls)", "", True, "", , acExportQualityPrint
'FormatExcel()
Dim xlApp As Object 'Excel.Application
Dim xlBook As Object 'Excel.Workbook
Dim xlSheet As Object 'Excel.Worksheet
With xlSheet
.Columns("A:A").Select = Selection.NumberFormat = "@"
.Columns("G:G").Select = Selection.NumberFormat = "$#,##0"
.Columns("H:H").Select = Selection.NumberFormat = "dd-mmm-yy hh:mm"
xlBook.Save
'*** uncomment to keep open
'xlBook.Close True
xl.DisplayAlerts = False
'True
End With
'DoCmd.Save
DoCmd.Close "Qry_Meeting_Export"
ExportandFormatinExcel_Click_Exit:
Exit Sub
ExportandFormatinExcel_Click_Err:
MsgBox Error$
Resume ExportandFormatinExcel_Click_Exit
End Sub
Many thanks