Access 2003 Office Links to Excel Column Names (Text1 Text35) Problem

[Roger]

I have an access 2004 application I have been using for a long time with
Office 2003 linking.

I have several reports I run and they work great. I use the Office Link to
Excel a lot with certain reports. This has worked great until recently.

Now it works, but the column names do not always come out correctly.

I get some column Names like Text Text ect. Not all the columns, just some.
No changes were made to the Report or Access that would have caused it.

I was wondering if anyone had any hints as to why this would have just
started up. I have always used Office 2003 and it was working fine until
about 2 months ago.

 

[Guest]

Hi Roger,
I am assuming that you are not using the TransferSpreadsheet function in Vba.
I prefer to use this rather than relying on Microsoft's built-in stuff which
is subject to random updates and therefore one day ceases to function without
explanation.

Its simple to use,
Create a command button and on its click event put the following;

Private Sub Command30_Click()
Dim oApp As Object
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, {yourtable},
{output file path}
Set oApp = GetObject({output file path})
oApp.Application.Visible = True
oApp.Parent.Windows(1).Visible = True
On Error Resume Next
End Sub
The 'oApp' bits will open the file after export.

The beauty of this is that your can export from a query or a table and in a
query be able to specify fieldnames that are different to the table
fieldnames.

Have a play, it may well solve your problems.