Export to excel, Multiple listbox --> multiple worksheets 1

[gavintennant]

Hi,

I have several forms with more than one list box on them. Basically, I already have some code that can take a list box and export it to an excel spreadsheet. My problem is that I want to export these multiple list boxes into one spreadsheet with multiple worksheets.
Can anyone out there help me please!

Thanks in advance!
Gavin

 

[Vika]

Following is what MS Access Help has to say about it. It basicly says that you can specify a worksheets when you do import from Excel Spredsheets (5.0, 7.0 and Access 97), but you can not specify it when you do export.


You can use the TransferSpreadsheet action to import or export data between the current Microsoft Access database and a spreadsheet file. You can also link the data in a Microsoft Excel spreadsheet to the current Microsoft Access database. With a linked spreadsheet, you can view and edit the spreadsheet data with Microsoft Access while still allowing complete access to the data from your Microsoft Excel spreadsheet program. You can also link to data in a Lotus 1-2-3 spreadsheet file, but this data is read-only in Microsoft Access.

Setting

The TransferSpreadsheet action has the following arguments.

Action argument Description
Transfer Type The type of transfer you want to make. Select Import, Export, or Link in the Transfer Type box in the Action Arguments section of the Macro window. The default is Import.
Spreadsheet Type The type of spreadsheet to import from, export to, or link to. You can select one of a number of spreadsheet types in the box. The default is Microsoft Excel 3.
Notes· If you want to import, export, or link spreadsheet data to or from Lotus 1-2-3 files, you must install the Lotus 1-2-3 driver from the Office 97 ValuPack. For information on installing this driver from the ValuPack, see About the Office 97 ValuPack.
· You can import from and link (read-only) to Lotus .WK4 files, but you can't export Microsoft Access data to this spreadsheet format. Microsoft Access also no longer supports importing, exporting, or linking data from Lotus .WKS or Microsoft Excel version 2.0 spreadsheets with this action. If you want to import from or link to spreadsheet data in Microsoft Excel version 2.0 or Lotus .WKS format, convert the spreadsheet data to a later version of Microsoft Excel or Lotus 1-2-3 before importing or linking the data into Microsoft Access.
Table Name The name of the Microsoft Access table to import spreadsheet data to, export spreadsheet data from, or link spreadsheet data to. You can also type the name of the Microsoft Access select query you want to export data from. This is a required argument.
If you select Import in the Transfer Type argument, Microsoft Access appends the spreadsheet data to this table if the table already exists. Otherwise, Microsoft Access creates a new table containing the spreadsheet data.
In Microsoft Access 97, you can't use an SQL statement to specify data to export when you are using the TransferSpreadsheet action. Instead of using an SQL statement, you must first create a query and then specify the name of the query in the Table Name argument.
File Name The name of the spreadsheet file to import from, export to, or link to. Include the full path. This is a required argument.
Microsoft Access creates a new spreadsheet when you export data from Microsoft Access. If the file name is the same as the name of an existing spreadsheet, Microsoft Access replaces the existing spreadsheet, unless you're exporting to a Microsoft Excel version 5.0, 7.0, or Excel 97 workbook. In that case, Microsoft Access copies the exported data to the next available new worksheet in the workbook.


If you are importing from or linking to a Microsoft Excel version 5.0, 7.0, or Excel 97 spreadsheet, you can specify a particular worksheet by using the Range argument.


Has Field Names Specifies whether the first row of the spreadsheet contains the names of the fields. If you select Yes, Microsoft Access uses the names in this row as field names in the Microsoft Access table when you import or link the spreadsheet data. If you select No, Microsoft Access treats the first row as a normal row of data. The default is No.
When you export a Microsoft Access table or select query to a spreadsheet, the field names are inserted into the first row of the spreadsheet no matter what you select in this argument.
Range The range of cells to import or link. Leave this argument blank to import or link the entire spreadsheet. You can type the name of a range in the spreadsheet or specify the range of cells to import or link, such as A1:E25 (note that the A1..E25 syntax no longer works in Microsoft Access 97). If you are importing from or linking to a Microsoft Excel version 5.0, 7.0, or Excel 97 worksheet, you can prefix the range with the name of the worksheet and an exclamation point; for example, Budget!A1:C7.


Note When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.


Remarks

You can export the data in Microsoft Access select queries to spreadsheets. Microsoft Access exports the result set of the query, treating it just like a table.
Spreadsheet data that you append to an existing Microsoft Access table must be compatible with the table's structure. Each field in the spreadsheet must be of the same data type as the corresponding field in the table, and the fields must be in the same order (unless you set the Has Field Names argument to Yes, in which case the field names in the spreadsheet must match the field names in the table).

This action is similar to pointing to Get External Data or Save As/Export on the File menu of the Database window and clicking Import or Link Tables. You can use these commands to select a source of data, such as Microsoft Access or a type of database, spreadsheet, or text file. If you select a spreadsheet, a series of dialog boxes appear, or a Microsoft Access wizard runs, in which you select the name of the spreadsheet and other options. The arguments of the TransferSpreadsheet action reflect the options in these dialog boxes or in the wizards.

Note If you query or filter a linked spreadsheet, the query or filter is case-sensitive.

If you link to an Excel spreadsheet open in Edit mode, Access will wait until the Excel spreadsheet is out of Edit mode before completing the link; there's no time-out.
To run the TransferSpreadsheet action in Visual Basic, use the TransferSpreadsheet method of the DoCmd object.

 

[gavintennant]

Thanks Vika,
I had tried the Outputto action aswel, never seen this TransferSpreadsheet command. I'll give this a go but I don't think this has the ability to create multiple worksheets. Thanks again though!

 

[gavintennant]

I managed to get this to work now, managed to take the original code and hack it about abit so it looked up lista and listb then created seperate worksheets on the same workbook. Clever eh, not as good as you ppl yet, but working on it!!

 

[Vika]

You're very welcome! I hope I helped.