Transfer Spreadsheet... setting parameters, etc.

[ThriftyFinanceGirl]

I have created a transfer spreadsheet procedure in which I used a saved
version of the transfer criteria (saved using the Access Wizard). I now need
to modify it, however when I go back into the wizard (to save new criteria)
the option to save the transfer specifics is not there anymore!

However, I would rather set all this in my code so that I can see the
specifics, thus making it easier to modify in the future. I can't find
anywhere in the MS Help what all the parameters are that you need to set to
do the TransferSpreadsheet command, any help on this? Here is my code below:
---------------------------------------------------------------
Public Function TaxExemptImport()

'Let's get the file name first....
Dim strFilter As String
Dim strInputFileName As String

strFilter = ahtAddFilterItem(strFilter, "Excel files (*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select file to Import...", _
Flags:=ahtOFN_HIDEREADONLY)

'make sure that the user did not CANCEL the open dialog box
If strInputFileName = vbNullString Then
Exit Function
Else


DoCmd.SetWarnings False
Application.Echo False
'bring data into the holding table
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"TaxExemptImport", strInputFileName
Forms!frmtaxexempt!img1.Visible = True
Forms!frmtaxexempt!lbl1.Visible = True
Forms!frmtaxexempt.TimerInterval = 2000
Application.Echo True

 

[ThriftyFinanceGirl]

I think I found it guys, had to look at a few websites, but if you have any
good tricks and tips here, they would still be appreciated!!

 

[ThriftyFinanceGirl]

Well, let's try this one more time, I now have all the arguments that I need,
but how do I tell it to import the SECOND worksheet? The range might be
different each time, and I'm afraid that they might rename the worksheet, but
it will always be the second worksheet. Any suggestions?

 

[ryguy7272]

This is a great resource for Importing/Exporting:
http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

I don't know that there's a way to specify the worksheet number - I only know
how to specify the name of the worksheet. Someone else here may know how to
format it to specify the sheet number. I'm pretty sure you could open Excel
in your VBA and find out the name of the 2nd worksheet. Something like this:

Dim wkbk As Excel.Workbook
Dim wkSheet As Excel.Worksheet
Dim oXLApp As Excel.Application

Set oXLApp = New Excel.Application
Set wkbk = oXLApp.Workbooks.Open("Excel file name here")
Set wkSheet = wkbk.Worksheets(1)
MsgBox wkSheet.Name
wkbk.Close
oXLApp.Quit
Set wkbk = Nothing
Set wkSheet = Nothing
Set oXLApp = Nothing

You need to add a reference to Excel if you do this - in the VBA code window
choose Tools, then References, then scroll down to Microsoft Excel and select
it.

I don't know if the worksheets collection is 0-indexed or not, so it may be
Worksheets(1) or Worksheets(2). I put the msgbox in there just so that the
sheet name is displayed for testing purposes.

Well, let's try this one more time, I now have all the arguments that I need,
but how do I tell it to import the SECOND worksheet? The range might be
different each time, and I'm afraid that they might rename the worksheet, but
it will always be the second worksheet. Any suggestions?

I have created a transfer spreadsheet procedure in which I used a saved
version of the transfer criteria (saved using the Access Wizard). I now need

[quoted text clipped - 33 lines]

Forms!frmtaxexempt.TimerInterval = 2000
Application.Echo True

 

[ThriftyFinanceGirl]

Thanks for the Resource!

This is a great resource for Importing/Exporting:
http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

I don't know that there's a way to specify the worksheet number - I only know
how to specify the name of the worksheet. Someone else here may know how to
format it to specify the sheet number. I'm pretty sure you could open Excel
in your VBA and find out the name of the 2nd worksheet. Something like this:

Dim wkbk As Excel.Workbook
Dim wkSheet As Excel.Worksheet
Dim oXLApp As Excel.Application

Set oXLApp = New Excel.Application
Set wkbk = oXLApp.Workbooks.Open("Excel file name here")
Set wkSheet = wkbk.Worksheets(1)
MsgBox wkSheet.Name
wkbk.Close
oXLApp.Quit
Set wkbk = Nothing
Set wkSheet = Nothing
Set oXLApp = Nothing

You need to add a reference to Excel if you do this - in the VBA code window
choose Tools, then References, then scroll down to Microsoft Excel and select
it.

I don't know if the worksheets collection is 0-indexed or not, so it may be
Worksheets(1) or Worksheets(2). I put the msgbox in there just so that the
sheet name is displayed for testing purposes.

Well, let's try this one more time, I now have all the arguments that I need,
but how do I tell it to import the SECOND worksheet? The range might be
different each time, and I'm afraid that they might rename the worksheet, but
it will always be the second worksheet. Any suggestions?

I have created a transfer spreadsheet procedure in which I used a saved
version of the transfer criteria (saved using the Access Wizard). I now need
[quoted text clipped - 33 lines]
Forms!frmtaxexempt.TimerInterval = 2000
Application.Echo True

 

[ThriftyFinanceGirl]

Thanks again Jim for all that you do!

I don't know that there's a way to specify the worksheet number - I only know
how to specify the name of the worksheet. Someone else here may know how to
format it to specify the sheet number. I'm pretty sure you could open Excel
in your VBA and find out the name of the 2nd worksheet. Something like this:

Dim wkbk As Excel.Workbook
Dim wkSheet As Excel.Worksheet
Dim oXLApp As Excel.Application

Set oXLApp = New Excel.Application
Set wkbk = oXLApp.Workbooks.Open("Excel file name here")
Set wkSheet = wkbk.Worksheets(1)
MsgBox wkSheet.Name
wkbk.Close
oXLApp.Quit
Set wkbk = Nothing
Set wkSheet = Nothing
Set oXLApp = Nothing

You need to add a reference to Excel if you do this - in the VBA code window
choose Tools, then References, then scroll down to Microsoft Excel and select
it.

I don't know if the worksheets collection is 0-indexed or not, so it may be
Worksheets(1) or Worksheets(2). I put the msgbox in there just so that the
sheet name is displayed for testing purposes.

Well, let's try this one more time, I now have all the arguments that I need,
but how do I tell it to import the SECOND worksheet? The range might be
different each time, and I'm afraid that they might rename the worksheet, but
it will always be the second worksheet. Any suggestions?

I have created a transfer spreadsheet procedure in which I used a saved
version of the transfer criteria (saved using the Access Wizard). I now need

[quoted text clipped - 33 lines]

Forms!frmtaxexempt.TimerInterval = 2000
Application.Echo True