The header is obliterated when MS access table exports to Excel?

[Lou_Reed]

When I export a table from the *.accdb files to Excel in it come out a textbook.xlx. An aalready created file with headers.



Now everything is alright meaning that all went over. However, it obliterated my
multiple line header during the export.

I have it start writing at 12 row and it does. However, anything above row 12 that was there before the export
action began is now gone.

How do I fix this.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[pbaldy]

Haven't tried to execute the code, but you realize that:

Set ws = xl.ActiveWorkbook.Sheets.Add

adds a new sheet. I suspect you'll find your header on the original sheet.

[Lou_Reed]

Okay, but I just want to preserve the headers. How can I fix it?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[pbaldy]

Use the sheet with the headers on it. Here's code from a db of mine, variables contain the file and sheet names:

xl.Workbooks.Open (strFile)
Set xlSheet = xl.Worksheets(strSheet)
xlSheet.Select

[Lou_Reed]

Could you elaborate a more on this. I am not sure how t use the three lines of VBA code that you gave me.

Respectfully,

Lou Reed

[Lou_Reed]

On my VBA code the lines are:

xl.Workbooks.Open ("C:\Users\xxxxx.xxxxxx\Desktop\testbook.xlsx")
Set ws = xl.ActiveWorkbook.Sheets.Add
ws.Select

my username is x'ed out.

Now your is

xl.Workbooks.Open (strFile)
Set xlSheet = xl.Worksheets(strSheet)
xlSheet.Select

So does that mean that my definition for strFile would be :

("C:\Users\xxxxx.xxxxxx\Desktop\testbook.xlsx") ?

Also what would strSheet be? sheet1?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[pbaldy]

Instead of:

Set ws = xl.ActiveWorkbook.Sheets.Add

try

Set ws = xl.Worksheets("NameOfYourSheetHere")

[Lou_Reed]

I am including a zipped file of my changed db and a zipped copy of the excel sheet that it exports to. When I run the db program I get a subscript out of range error.
I do not know what the are talking about. It compiles with no problem.

Any way to fix this?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[pbaldy]

You haven't changed the sheet name to yours ("Expanded Tracker").

[Lou_Reed]

I am attaching a jpeg file to point out the line of code in which the programs crashes with a subscript out of range error.

I just do not know what os wrong with this line of code.

Any help appreciated.

Respectfully,

Lou Reed

[pbaldy]

The name of the sheet in your Excel file isn't "Sheet1", he repeated.

[Lou_Reed]

I understand what you say, I used "Expanded Tracker" instead of "sheet1". The VBA code complied with no errors and it ran to finished with no runtime errors.

When I checked testbook.xlxs it did not clear the header files - that is good, but it did not write anything into testbook.xlxs. So I have header files intact, but no data in the excel spreadsheet.


It as I said ran to the finished sign, but it did not write anything? What is going on?

The files are uploaded for your examination.


Any help appreciated. Thanks in advance.


Respectfully,


Lou Reed

[Lou_Reed]

I think that I know the issue here. I just do not know how to repair it. When I exported a table to Excel and used the Production Tracker table in MS Access everything went fine. The data went over and saved the header rows just fine.

However, when I used the expanded superstore table it tool longer (as one would expect), it saved the header rows as I wanted. It however did not seem to write any data. I just got the textbook.xlsx file back again as it was when I started the whole process. It was as if nothing had changed.

I now believe it works, it just does not work on the Superstore table with thousands of rows.

That just refused to write. I am not sure why and that is my question. What is happening? How do I fix it.

It seems whether it works or not depends on size of the table.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[Lou_Reed]

I need an answer to this question. Dows he expert of an Access table to Excel have a size limit? It seems to.

Respectfully,

Lou Reed

[Lou_Reed]

I am sorry to have to ask gain, but here goes:

When exporting an MS Access table to MS Excel spreadsheet is there a size limit? There seems to be.

If there is a size limit how do I raise it?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed