Exporting and formatting Excel from Access

[Frankie_The_Flyer]

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

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

 

[Alansidman]

Look at Bob Larsen's site here: http://www.btabdevelopment.com/ts/default.aspx?PageId=10

He has provided several options for this issue.

 

[Frankie_The_Flyer]

Thanks Alansidman. My brain is a bit too full with this at the moment! I tried pasting the whole lot into a Module etc but ended up with the database just bringing up repeate error warnings; one without any wording on it.
I was hoping there may be something a bit easire as I really am at the blunt end of VB.
The other issue I have with exporting the form was that it sent over all the header information into columns which wrecked the spreadsheet use, which is why I went to tryiny to export from a query. If I could just get rid of the "Type Mismatch" I'd be home and dry!

 

[Alansidman]

Does your query run with out the Type Mismatch error?

 

[Rx_]

http://www.access-programmers.co.uk/forums/showthread.php?t=249670
See your favorite SQL Statement here...

Strongly suggest using a recordset with the query. As someone who does this often, my preference is to write a query, copy the SQL - paste the SQL into the code.
It helps to use variables in the SQL or just know that the query wan't changed.

The built in functions just don't offer troubleshooting (error codes). There are plenty of examples to copy out there including Bob Larson's excellent site.

Once you copy some code, paste in the SQL statement, it will save you time and headaches. That is just my recommendation.

If you are trying to convert a text to an Excel TEXT to an Excel NUMBER - so formatting can work -
See my post at http://www.access-programmers.co.uk/forums/showthread.php?t=249664

 

[Frankie_The_Flyer]

Not too sure Alan. Sometimes it seems to transfer data to Excel before it throws up the Type Mismatch, other times it doesn't pass the data over before the error comes up.

 

[Frankie_The_Flyer]

Thanks for the idea RX but times running out to get the db into use and I've little idea of what I'm doing!
I may just shelve this bit until I have more time to try and better understand coding. Shame really as it's the last part of the db.
Still, I'm proud of what I have achieved!!!

 

[Alansidman]

Cross Posted at: http://www.excelforum.com/access-pr...e-mismatch-when-exporting-query-to-excel.html

Please read this: http://www.excelguru.ca/content.php?184

 

[Frankie_The_Flyer]

Hi Alan

Slightly different approach on the other site. I was struggling severely with trying to resolve this issue and having introduced the database to the team, realised I had to find a way out of it as soon as possible, so I posted asking for someone to have a go at writing a code for me, rather than trying to sort out my own.
As an aside, I entered the other site from my iPad and it didn't appear to accept my post and no confirmation of the post going up came in on the e-mail.

As a second aside, I resolved the issue by using the macro builder in Access 2007 which gives you and option of exporting to Excel with just a couple of clicks!

Thanks for the help anyway