Access into Excel VBA problem

[hatstand]

Hi, I've read and created a macro that imports a simple CrossTab Query into Excel. I just recorded it and played it back. Works perfectly.

But when I tried it again with more columns headers (50 column headers that are time broken down into 15 minute intervals) and run the code. Nothing happens. I have noticed that the code does not include all the headers, only up to 14:00. It should go up to 19:45.

My question is do I simple write in the missing code or is there a limit on how many headers?

 

[Martins]

Hi hatstand,

I am fairly new to VBA, but it seems to me that it might be easier to accomplish what you need by exporting from Access rather than importing from Excel (which is what it appears you are doing). Of course, depending on what you are doing, this may not work for you. If you are using Excel 2007 or 2010 this could be accomplished as follows:

In a module in Access-

Sub ExportQuery()

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
          "MyQueryName", "C:\Data\MyFileName.xlsx"

End Sub

The file extension and acSpreadSheetType would change if you are using an older version of MS Office.

I hope I am understanding your problem correctly and that this might be of some help!

Cheers,
Martins