Using VB to Export a Table Formatted? Access 97 1

[lifter10]

Does anyone know the code to be able to export a table from Access into Excel in the formatted style when you export using the Access interface? I'm currently using the TransferSpreadsheet function, but that obviously doesn't work. I'm not able to use a macro to automate the export because my table has about 22,000 records and it always tells me that it's too large to export. Any help would be appreciated!

 

[BNPMike]

export a table from Access into Excel in the formatted style

Can you say what you mean by formatted style. Access tables don't have styles. Do you mean keeping the same datatypes?

I'm not able to use a macro to automate the export because my table has about 22,000 records and it always tells me that it's too large to export.

Are you saying you only get this error when you use a Macro ie you can do it manually ok?

 

[lifter10]

When you go to File -> Save As/Export, then you chose to save as an Excel file, it gives you 2 checkboxes by the Export and Cancel buttons. One is Save Formatted and the other is Autostart. When I export this table I want it to be in that formatted style (It simply highlights the column headings and keeps the same column widths and formatted style you had in Access).

I cannot use the File -> Save As/Export -> Save Formatted because it says the export is too large. I cannot use the macro to automate the process because it says it's too large as well. I can copy the data directly and paste it too Excel, but the data is all jumbled. I read somewhere that when Access tries to export it automatically defaults to Excel 5 which can only support something like 16,000 rows.

I was wondering if there was some code in VB to use that would allow me to save that table in the formatted style provided above. I know it's not really a big deal, but I run this process many times and I don't want to go into this file and format the columns in Excel everytime. Thanks for the response!

Chris

 

[RippleC]

Hi,
I am new to all this and exporting to excel from access is the problem I am having. Here is what I would like to do.
I would like to
1. EXPORT out an ACCESS TABLE as a TAB DELIMINATED file
2. OPEN the TAB DELIMINATED file as an EXCEL file
3. SORT the EXCEL file by a field
Ideally, I would like to make this as part of a macro is access as there is a vb code I am running in the macro. After the vb code is done running I would like do carry out the three steps I mentioned above. Other than macro if there is a simpler way I can do this in VB / SQL. Whatever method that would allow me to perform this tasks.
Any help on this will be greatly appreciated.
Thank you.
Ripple

 

[lifter10]

Ripple here is some code that should help you export from Access in a delimited form:

DoCmd.TransferText acExportDelim, Specification Name, Table Name, Directory, -1 (for field names)


This is an example of how I use the code:

DoCmd.TransferText acExportDelim, "Export Specification", _
"OutputTable", "T:\Investments\MGMTNLI\_Adjusted IDW Data.txt", -1

Where "Export Specification" is the specification format I've made in Access, "OutputTable" as the table or query I want exported, and the path where I want the file exported too. That should take care of step 1.

To open in Excel simply find your file, open it and run through the steps it gives you. You can then sort on the field names in the file. Hope this helps.

Chris

 

[RippleC]

Thanks Chris.
That worked perfectly with what I was doing, now I am on step 2 of trying to have the code open these text files in excel. I am thinking about using something along the idea below, but am still struggling. If you have any suggestions, it will be helpful.

Dim ApExcel As Object
Set ApExcel = CreateObject("Excel.application") ApExcel.Visible = True

Thanks a bunch
Ripple