Solved: excel and access

[philfer]

Let me paint a picture.

I am sitting at my desk using excel. I want to get excel to open an access DB,transfer data from three sheets into three tables, run two queries and paste the results in two sheets in the workbook I started out in.

Can this be done?

[DarkSprout]

Yup,

See Link:
http://office.microsoft.com/en-us/access/HP010950951033.aspx

[philfer]

thanks for that info but is there a way to do this using VBA

I tried the TransferSpreadsheet method. It allows me to import sheets into tables but wont let me get query results to drop into an open workbook

[X-BRichard-X]

Use the acExport argument in your DoCmd.TransferSpreadsheet method and you should be fine. Here's the exact VBA help on this method:

The TransferSpreadsheet method carries out the TransferSpreadsheet (mk:@MSITStore:vbaac10.chm::/html/acactTransferSpreadsheet.htm) action in Visual Basic.
expression.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)
expression Required. An expression that returns one of the objects in the Applies To list.
TransferType Optional AcDataTransferType (mk:@MSITStore:C:\Program%20Files\Microsoft%20Office\OFFICE11\1033\vbaac10. chm::/html/acmthactTransferSpreadsheet.htm##).
AcDataTransferType can be one of these AcDataTransferType constants.
acExport
acImport default
acLink
If you leave this argument blank, the default constant (acImport) is assumed.
SpreadsheetType Optional AcSpreadSheetType (mk:@MSITStore:C:\Program%20Files\Microsoft%20Office\OFFICE11\1033\vbaac10. chm::/html/acmthactTransferSpreadsheet.htm##).
AcSpreadSheetType can be one of these AcSpreadSheetType constants.
acSpreadsheetTypeExcel3
acSpreadsheetTypeExcel4
acSpreadsheetTypeExcel5
acSpreadsheetTypeExcel7
acSpreadsheetTypeExcel8 default
acSpreadsheetTypeExcel9 default
acSpreadsheetTypeLotusWJ2 - Japanese version only
acSpreadsheetTypeLotusWK1
acSpreadsheetTypeLotusWK3
acSpreadsheetTypeLotusWK4
Note You can import from and link 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 by using this method.
If you leave this argument blank, the default constant (acSpreadsheetTypeExcel8) is assumed.
TableName Optional Variant. A string expression (mk:@MSITStore:C:\Program%20Files\Microsoft%20Office\OFFICE11\1033\vbaac10. chm::/html/acmthactTransferSpreadsheet.htm##) that's the name of the Microsoft Access table you want to import spreadsheet data into, export spreadsheet data from, or link spreadsheet data to, or the Microsoft Access select query (mk:@MSITStore:C:\Program%20Files\Microsoft%20Office\OFFICE11\1033\vbaac10. chm::/html/acmthactTransferSpreadsheet.htm##) whose results you want to export to a spreadsheet.
FileName Optional Variant. A string expression that's the file name and path of the spreadsheet you want to import from, export to, or link to.
HasFieldNames Optional Variant. Use True (–1) to use the first row of the spreadsheet as field names when importing or linking. Use False (0) to treat the first row of the spreadsheet as normal data. If you leave this argument blank, the default (False) is assumed. When you export Microsoft Access table or select query data to a spreadsheet, the field names are inserted into the first row of the spreadsheet no matter what you enter for this argument.
Range Optional Variant. A string expression that's a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.
UseOA Optional Variant.