TransferSpreadsheet trouble

[slmndr]

CODE BELO
I am trying to import data from several multisheet Excel spreadsheets
I get an error at the line highlighted in red (See code window). Fo
some reason VBA/Access is changing the *!* in the string strClient int
a *$*. Obviously the *!* is required to make a reference to cells on
specific sheet within an Excel file.

Running this code produces an error on the first loop throug
intClient. The error is:

Run-Time error '3011':
The Microsoft Jet database engine could not find the object
'Client1$$D$24'. Make sure the object exists and that you spell it
name
and the path name correctly.

I have not experienced any other problems with any of the coding. I ca
successfully import from a file with only one spreadsheet only using
cell range without the sheet name. It is not possible to break out th
sheets I am trying to import to separate files, as they are prepared b
another group.

Strangely this method and syntax are listed in the VBA/Access hel
file, but of course it doesn't work.

edit: The question boils down to this: If I am using Access 2000 an
Excel 2000, how can I make this work and maintain the *!* where it i
supposed to be?



Code
-------------------
Private Sub ImportForecast_Click()

Dim intDay As Integer
Dim strDay As String
Dim intClient As Integer
Dim strClient As String
Dim intSched As Integer
Dim intStart As Integer
Dim intEnd As Integer
Dim strPath As String
Dim strDate As String
Dim datDate As Date
Dim strRange As String

datDate = Forms!frm_Forecast.txt_Date
strDate = Left(datDate, 2) & Mid(datDate, 4, 2)
strPath = "G:\TOC\Forecast\" & strDate & "\"

'On Error GoTo FileGetError <<commented out for debugging>>

'intDay loops through the forecast files in order by day.
For intDay = 1 To 7
'Set strDay to the file name for that day
Select Case intDay
Case 1
strDay = "1 Monday.xls"
Case 2
strDay = "2 Tuesday.xls"
Case 3
strDay = "3 Wednesday.xls"
Case 4
strDay = "4 Thursday.xls"
Case 5
strDay = "5 Friday.xls"
Case 6
strDay = "6 Saturday.xls"
Case 7
strDay = "7 Sunday.xls"
End Select

strPath = strPath & strDay

Select Case intDay
Case 1, 2, 3, 4, 5 'Monday - Friday
intStart = 24
intEnd = 46
Case 6 'Saturday
intStart = 28
intEnd = 40
Case 7 'Sunday
intStart = 32
intEnd = 40
End Select

For intClient = 1 To 9
Select Case intClient
Case 1
strClient = "Client1!$D$"
Case 3
strClient = "Client2!$D$"
Case 4
strClient = "Client3!$D$"
Case 6
strClient = "Client4!$D$"
Case 10
strClient = "Client5!$D$"
Case 17
strClient = "Client6!$D$"
Case 18
strClient = "Client7!$D$"
Case 21
strClient = "Client8!$D$"
Case 23
strClient = "Client9!$D$"
End Select

'Loop by Scheduled time to extract the forecasts
intSched = intStart
Do While intSched < intEnd
strRange = strClient & intSched
* DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"tbl_zzz_Ftemp", strPath, 0, strRange, 1*
DoCmd.OpenQuery "qry_importForecast", acViewNormal, acEdit
DoCmd.OpenQuery "qry_clrTmpForecast", acViewNormal, acEdit
If intSched = 40 Then intSched = 45 Else intSched = intSched + 4
Loop
Next intClient
Next intDay

FileGetError:
Exit Sub
End Su

 

[John Nurick]

AFAIK you need to specify a sheet and cell range in the form

Sheet1$A10:E20
or for a single cell
Sheet1$D99

 

[slmndr]

Perfect solution. Can't believe I overlooked that. Thanks so much