Transferspreadsheet

[simon.parker]

Hello,

I have been given the task of importing data from various Excel
spreadsheets into an Access 2003 database. This has pretty much been
completed OK but now the user has asked for the data to be put
together into a query and exported into a new spreadsheet. Again this
would have been OK but she would like the spreadsheet to be opened
within Access (or on its own) instead of being saved down, giving the
user the option of saving the spreadsheet anywhere on the system.

For the life of me I can't work out how to do this. Originally I used
the Transferspreadsheet function but this appears only to give the
option of saving the file to disk.

If anyone out there could give me the solution to my problem I would
be very grateful.

Thanks,

Simon

 

[fredg]

Hello,

I have been given the task of importing data from various Excel
spreadsheets into an Access 2003 database. This has pretty much been
completed OK but now the user has asked for the data to be put
together into a query and exported into a new spreadsheet. Again this
would have been OK but she would like the spreadsheet to be opened
within Access (or on its own) instead of being saved down, giving the
user the option of saving the spreadsheet anywhere on the system.

For the life of me I can't work out how to do this. Originally I used
the Transferspreadsheet function but this appears only to give the
option of saving the file to disk.

If anyone out there could give me the solution to my problem I would
be very grateful.

Thanks,

Simon

In a Module:

Public Sub Export()
' This will export data to a spreadsheet.
' The user can enter the path and name of the spreadsheet.

Dim Where As String

' Enter the path and name of the new spreadsheet:
' i.e. c:\MyFolder\MySpreadsheet.xls

Where = InputBox("Path and File Name")

' Transfer the Access query data to the new spreadsheet.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97,
"QueryName", Where, True

' Open the new spreadsheet.

Application.FollowHyperlink Where


End Sub