TransferSpreadsheet questions

[annysjunkmail]

I have 2 questions with TransferSpreadsheet.

1. How do I open and select a sheet on the spreadsheet that I have
just exported? After gleaming the Newsgroups I have got this far but
can't work out to select a sheet...

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MyQuery",
"d:\Temp\MySpreadsheet.xls", , True

Dim xlObj As Object
Set xlObj = CreateObject("excel.application")
'open the template file
xlObj.Workbooks.Open "d:\Temp\MySpreadsheet.xls"'Start Excel and show
xlObj.Visible = True

2. In conjunction with the Transferspreasheet command is it possible
to allow the user to select the location to save the exported
spreadsheet instead of inserting the default path of
"d:\Temp\MySpreadsheet.xls"? The reason that I ask is because the
database is stored on a server, which is used by other staff, and it is
possible that my spreadsheet could easily be overwritten with another
users' spreadsheet.

Many thanks
Tony

 

[Ken Snell [MVP]]

Answers inline...

--

Ken Snell
<MS ACCESS MVP>

I have 2 questions with TransferSpreadsheet.

1. How do I open and select a sheet on the spreadsheet that I have
just exported? After gleaming the Newsgroups I have got this far but
can't work out to select a sheet...

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MyQuery",
"d:\Temp\MySpreadsheet.xls", , True

Use the Range argument of the TransferSpreadsheet method to specify the
sheet name. If there are no spaces in the sheet name:
"SheetName!"

If there are spaces in the sheet name:
"'SheetName'!"

Dim xlObj As Object
Set xlObj = CreateObject("excel.application")
'open the template file
xlObj.Workbooks.Open "d:\Temp\MySpreadsheet.xls"'Start Excel and show
xlObj.Visible = True

2. In conjunction with the Transferspreasheet command is it possible
to allow the user to select the location to save the exported
spreadsheet instead of inserting the default path of
"d:\Temp\MySpreadsheet.xls"? The reason that I ask is because the
database is stored on a server, which is used by other staff, and it is
possible that my spreadsheet could easily be overwritten with another
users' spreadsheet.

You can replace the hardcoded path/file for the spreadsheet with a function
that will get the information from the user. If you want the user to type in
data, use the InputBox function. If you want to allow the user to navigate
to a folder, you can use an API function (see
http://www.mvps.org/access/api/api0001.htm at "The Access Web" for how to
use the Open File Dialog).

 

[annysjunkmail]

Excellent advice Ken and thank you.

Used and implemented both ideas and seems to be working fine.
I am posting the solution for the benefit of other users.

'Code start
'Ask for SaveFileName
Dim strSaveAsFilter As String
Dim strSaveAsFileName As String
strFilter = ahtAddFilterItem(strSaveAsFilter, "Excel Files (*.xls)",
"*.xls")
strSaveAsFileName = ahtCommonFileOpenSave( _
OpenFile:=False, _
Filter:=strSaveAsFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MyQuery",
strSaveAsFileName, , "-1", True

Dim xlObj As Object
Set xlObj = CreateObject("excel.application")
'open the template file
xlObj.Workbooks.Open strSaveAsFileName
'Start Excel and show
xlObj.Visible = True

'Code end

Many thanks
Tony