Importing from Excel 2007 into Access 2007: Object Required

[KC_Cheer_Coach]

I gleaned some code from the net and dropped in the specifics for my db. This
db is to hold data for quarterly reporting. I am trying to do the following:

Import specific spreadsheets from Excel 2007 workbooks based on information
entered on the main form and append the data to a single table in the
database. Each workbook has a different number of worksheets, each worksheet
has a different range of data, and each workbook has beginning and ending
worksheets that I do not need. There are a lot of things I have to define in
order for it to pull the correct file and this is working, but when it gets
to the section where it is to import data from the sheets to the table in
Access, it bugs out with msg "Run-time error '424': Object required". Can you
help? Here is the code and thanks in advance (KCCC):


Private Sub cmdImport_Click()

Dim blnHasFieldNames As Boolean, blnExcel As Boolean, blnReadOnly As Boolean
Dim lngCount As Long, lngStartSheet As Long, lngEndSheet As Long
Dim objExcel As Object, objWorkbook As Object, objRange As Object,
objWorksheet As Object
Dim colWorksheets As Collection
Dim intNoOfSheets As Integer
Dim strTable As String, strPassword As String, strWorksheetName As String
Dim strPath As String, strMainPath As String, strFilePath As String,
strAddPath As String, strMMM As String
Dim strRun As String, strExtension As String, strFilename As String

'Establish an Excel aapplication object
On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set objExcel = CreateObject("Excel.Application")
blnExcel = True
End If
Err.Clear
On Error GoTo 0

'True if the first row in Excel worksheet has fieldnames
blnHasFieldNames = True

'Set the main part of the path (root)
strMainPath = "\\Share\Company\Fin\01\Service\"

'Set the middle part of the path
If Left$((Me.cboFilename), 2) = "CA" Then
strFilePath = "IV\CA Verif\VCC\"
Else
If Left$((Me.cboFilename), 2) = "IP" Then
strFilePath = "IV\IP Verif\VCC\"
Else
If Left$((Me.cboFilename), 2) = "PD" Then
strFilePath = "IV\PD Verif\VCC\"
Else
If Left$((Me.cboFilename), 2) = "AA" Then
strFilePath = "IV\IP Verif\AA and MS\"
Else
If Left$((Me.cboFilename), 2) = "MS" Then
strFilePath = "IV\IP Verif\AA and MS\"
Else
If Left$((Me.cboFilename), 2) = "CT" Then
strFilePath = "PCV\CT Verif\VCC\"
Else
If Left$((Me.cboFilename), 2) = "PR" Then
strFilePath = "PCV\PCVC\"
End If
End If
End If
End If
End If
End If
End If

'Set the specific date folders of the path
strAddPath = Left((Me.txtUsagePeriod), 4) & "\" &
Right$((Me.txtUsagePeriod), 2) & "_"

'Set the 3 char month name
If Right$((Me.txtUsagePeriod), 2) = "01" Then
strMMM = "Jan"
Else
If Right$((Me.txtUsagePeriod), 2) = "02" Then
strMMM = "Feb"
Else
If Right$((Me.txtUsagePeriod), 2) = "03" Then
strMMM = "Mar"
Else
If Right$((Me.txtUsagePeriod), 2) = "04" Then
strMMM = "Apr"
Else
If Right$((Me.txtUsagePeriod), 2) = "05" Then
strMMM = "May"
Else
If Right$((Me.txtUsagePeriod), 2) = "06" Then
strMMM = "Jun"
Else
If Right$((Me.txtUsagePeriod), 2) = "07" Then
strMMM = "Jul"
Else
If Right$((Me.txtUsagePeriod), 2) = "08" Then
strMMM = "Aug"
Else
If Right$((Me.txtUsagePeriod), 2) = "09" Then
strMMM = "Sep"
Else
If Right$((Me.txtUsagePeriod), 2) = "10" Then
strMMM = "Oct"
Else
If Right$((Me.txtUsagePeriod), 2) = "11" Then
strMMM = "Nov"
Else
If Right$((Me.txtUsagePeriod), 2) = "12" Then
strMMM = "Dec"
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

'Set the starting and ending sheet numbers for each filename in Excel
If ((cboFilename = "IP Check") And (Right$((Me.cboRunNo), 1) = 1)) Then
lngStartSheet = 6
lngEndSheet = 35
Else
If ((Me!cboFilename = "IP Check") And (Right$((Me.cboRunNo), 1) <> 1)) Then
lngStartSheet = 2
lngEndSheet = 35
Else
If Me.cboFilename = "AA Check" Then
lngStartSheet = 2
lngEndSheet = 5
Else
If Me.cboFilename = "MS Check" Then
lngStartSheet = 2
lngEndSheet = 6
Else
If Me.cboFilename = "PD Check" Then
lngStartSheet = 2
lngEndSheet = 2
Else
If Me.cboFilename = "CT Check" Then
lngStartSheet = 2
lngEndSheet = 2
Else
If Me.cboFilename = "PCCheck" Then
lngStartSheet = 2
lngEndSheet = 31
Else
If Me.cboFilename = "PCDaily Check" Then
lngStartSheet = 2
lngEndSheet = 33
Else
If Me.cboFilename = "CA Check" Then
lngStartSheet = 3
lngEndSheet = 5
End If
End If
End If
End If
End If
End If
End If
End If
End If

'Set the run number for the entire path
If Right$((Me.cboRunNo), 1) <> 1 Then
strRun = "R" & Right$((Me.cboRunNo), 1) & "_"
Else
strRun = ""
End If

'Set the file extension
If (Me.cboFilename) = "IP Check" Then
strExtension = ".xlsm"
Else
strExtension = ".xlsx"
End If

'Set the full filename
strFilename = (Me.cboFilename) & "_" & strRun & (Me.txtUsagePeriod) &
strExtension

'Set the entire path
strPath = strMainPath & strFilePath & strAddPath & strMMM & " " &
Left$((Me.txtUsagePeriod), 4) _
& "\" & strFilename

'Set the table name that all the data is to be appended to
strTable = "tblAllRuns"

'Set a password for the Excel files
'(e.g. if none: strPassword = vbNullString)
'(e.g. if password: strPassword = "passwordtext")
strPassword = vbNullString

'Open Excel in read-only mode
blnReadOnly = True

'Open the Excel file and read the worksheet names into a collection in Access
Set colWorksheets = New Collection
Set objWorkbook = objExcel.Workbooks.Open(strPath, , blnReadOnly, ,
strPassword)
intNoOfSheets = objWorkbook.Worksheets.Count
For lngCount = lngStartSheet To lngEndSheet
colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
Next lngCount

'Close Excel without saving the file, and clean up the Excel objects
objWorkbook.Close False
Set objWorkbook = Nothing
If blnExcel = True Then
objExcel.Quit
Set objExcel = Nothing
End If

'Import the data from each worksheet into the table
For Each objWorksheet In colWorksheets
Set objRange = objWorksheet.UsedRange
strWorksheetName = objWorksheet.Name & "!" & objRange.Address(False, False)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblAllRuns",
strFilename, True, strWorksheetName
Next

'Delete the collection from Access
Set colWorksheets = Nothing

'Create Message box popup to show import is complete
MsgBox "Your files have been imported", vbOKOnly

End Sub

 

[Ken Snell]

The problem is here:

Set objRange = objWorksheet.UsedRange

The object that you added to the collection does not retain all the
properties of the worksheet object itself. Your code is just storing the
name of the worksheet into your objWorksheet collection. So there is no
..UsedRange property to be set to the objRange object, and thus the objRange
object is never "set".

You'll need to store the .UsedRange property's "string" when you store the
worksheet name. I would recommend a second collection object to store the
range strings, and then you can read both collections to get the desired
information for your import.

 

[KC_Cheer_Coach]

Thank you. I will work on that today.

ttyl
KCCC

The problem is here:

Set objRange = objWorksheet.UsedRange

The object that you added to the collection does not retain all the
properties of the worksheet object itself. Your code is just storing the
name of the worksheet into your objWorksheet collection. So there is no
..UsedRange property to be set to the objRange object, and thus the objRange
object is never "set".

You'll need to store the .UsedRange property's "string" when you store the
worksheet name. I would recommend a second collection object to store the
range strings, and then you can read both collections to get the desired
information for your import.

--

Ken Snell
http://www.accessmvp.com/KDSnell/





.

 

[KC_Cheer_Coach]

Thanks! I appreciate it.

I'm going to offer some coding suggestion that are not exactly about the
question at hand, but may be helpful. Select Case has advantages over nested
Ifs. For instance (truncated code):

Select Case Left$((Me.cboFilename), 2)
Case "CA"
strFilePath = "IV\CA Verif\VCC\"
Case "IP"
strFilePath = "IV\IP Verif\VCC\"
Case "PD"
strFilePath = "IV\PD Verif\VCC\"
End Select

You can use If within Select Case:

Select Case Me.cboFilename
Case "IP Check"
lngEndSheet = 35
If Right$(Me.cboRunNo, 1) = 1 Then
lngStartSheet = 6
Else
lngStartSheet = 2
End IF
Case "AA Check"
lngStartSheet = 2
lngEndSheet = 5
Case "MS Check"
lngStartSheet = 2
lngEndSheet = 6
End Select

Also, the Switch function can be useful:

Dim strM as String
strM = Right$(Me.txtUsagePeriod), 2)

strMMM = Switch(strM = "01", "Jan",strM = "02", "Feb", strM = "03", "Mar")



--
Message posted via AccessMonster.com


.

 

[KC_Cheer_Coach]

Ken,

I really wanted to write back and say "I GOT IT!" However, no matter what I
try, I am not setting the object right and I don't know why. I have tried so
many things to pull the range into a collection and it gives me the same
error. Do you have any suggestions?

I am going to keep trying. Hope to hear from you soon.

KCCC

 

[KC_Cheer_Coach]

I thought I posted this earlier, but it is not showing up in the discussion
so I am posting it again.

I am having a problem storing the UsedRange. No matter how I write it, there
is always an object not set.

Any suggestions on how to store this?

KCCC

 

[Ken Snell]

OK. Let's see what we can do to help -- watch for wrapping of the code lines
by the newsreader:

Let's add these steps to your "dim" code block:
--------------------
Dim colWkshtUsedRange As Collection
Dim lngWorksheetItem As Integer

To this code block, I've added steps "delimited" by comment steps:
--------------------
'Open the Excel file and read the worksheet names into a collection in
Access
Set colWorksheets = New Collection
'****next step is new
Set colWkshtUsedRange = New Collection
Set objWorkbook = objExcel.Workbooks.Open(strPath, , blnReadOnly, ,
strPassword)
intNoOfSheets = objWorkbook.Worksheets.Count
For lngCount = lngStartSheet To lngEndSheet
colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
****next step is new
colWkshtUsedRange.Add objWorkbook.Worksheets(lngCount).UsedRange
Next lngCount


Now, modify this code block to what I show below:
--------------------------------
'Import the data from each worksheet into the table
For Each objWorksheet In colWorksheets
Set objRange = objWorksheet.UsedRange
strWorksheetName = objWorksheet.Name & "!" & objRange.Address(False,
False)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12,
"tblAllRuns", strFilename, True, strWorksheetName
Next
'Delete the collection from Access
Set colWorksheets = Nothing

'*****replacement code for above block:
'Import the data from each worksheet into the table
For lngWorksheetItem = 1 To colWorksheets.Count
strWorksheetName = _
IIf(colWorksheets(lngWorksheetItem) Like "*'*" Or _
colWorksheets(lngWorksheetItem) Like "* *", "'" & _
colWorksheets(lngWorksheetItem) & "'", _
colWorksheets(lngWorksheetItem)) & "!" & _
colWkshtUsedRange(lngWorksheetItem)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12,
"tblAllRuns", strFilename, True, strWorksheetName
Next lngWorksheetItem
'Delete the collection from Access
Set colWorksheets = Nothing
Set colWkshtUsedRange = Nothing

--

Ken Snell
http://www.accessmvp.com/KDSnell/

 

[KC_Cheer_Coach]

Ken,

I really appreciate all your help. I made the suggested updates and now when
I hit the Import button, it doesn't do anything...no errors and acts like it
has nothing at all to think about! I am going to keep working on it. My
deadline is fast approaching, too. I can always go back to defining every
workbook, every sheet, and every range in a ton of macros (like the old db)
but this is what the enhancement is for.

KCCC

 

[KC_Cheer_Coach]

Ken,

After I typed it all in correctly, I am getting "Error 424. Object
Required." When I debug, it highlights this portion, pointing to the
"colWkshtUsedRange(lngWorksheetItem)" line:

strWorksheetName = _
IIf(colWorksheets(lngWorksheetItem) Like "*'*" Or _
colWorksheets(lngWorksheetItem) Like "* *", "'" & _
colWorksheets(lngWorksheetItem) & "'", _
colWorksheets(lngWorksheetItem)) & "!" & _
colWkshtUsedRange(lngWorksheetItem)

Any other suggestions?

Thanks!

 

[Ken Snell]

Sorry... typo on my part.... not used to using .UsedRange property from
EXCEL.

Corrected code block:

'Open the Excel file and read the worksheet names into a collection in
Access
Set colWorksheets = New Collection
'****next step is new
Set colWkshtUsedRange = New Collection
Set objWorkbook = objExcel.Workbooks.Open(strPath, , blnReadOnly, ,
strPassword)
intNoOfSheets = objWorkbook.Worksheets.Count
For lngCount = lngStartSheet To lngEndSheet
colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
****next step is new -- REVISED to add .Address to .UsedRange
colWkshtUsedRange.Add
objWorkbook.Worksheets(lngCount).UsedRange.Address
Next lngCount

 

[KC_Cheer_Coach]

This morning, it was looking at my C drive for the path then I noticed I
needed to change the strFilename to strPath in the DoCmd.TransferSpreadsheet
line.

Now with adding the .Address to the .UsedRange it is getting closer. The
first sheet name that it should bring in is '01) 22_24 LTY' and the range is
$A$1:$I$26. The error now is:

Run-time error '3011':
The Microsoft Office Access database engine could not find the object ''01]
22_24 LTY'$$A41:$I$26'. Make sure the object exists and that you spell its
name and the path name correctly.

Debug takes me to the DoCmd.TransferSpreadsheet line item.
Immediate window shows correct path and sheet name.

I thought maybe it was the ) in the sheet name since it is a bracket in the
error, but it is correct in the immediate window. If I change it to something
else, I get the same error but with the new character in place of the bracket.

Can you think of anything that would cause this? It just baffles me b/c the
immediate window shows the right thing. I thought maybe it was a problem that
the workbook is shared and the sheets are protected, but if that was a
problem then it wouldn't have shown the correct range to begin with.

Thanks!
KCCC

 

[Ken Snell]

Well, I've done some experimenting tonite on this issue because I don't have
much experience with trying to import a specific cell block from a specific
worksheet. Your setup fails for me, too.

I think I have the reason for why it fails. This is the explanation for the
Range argument from Help file:

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.


Based on the above, you can only specify
(1) a range that is defined in the workbook; or

(2) a worksheet name (followed by $) because a worksheet is interpreted as a
Range when you do this, and EXCEL automatically assigns a default Range to a
worksheet name; or

(3) a specific block of cells.


But you cannot specify a worksheet name AND a block of cells. Bummer.

What you would need is a defined Name (Range) for the specific cells on the
specific worksheet to be in the workbook. My guess, though, is that this
range of cells will be different each time? and that you would not want to
manually create the Name in each workbook for each worksheet's UsedRange
before you run your code?

Assuming that you want this to be automated, you'd need to use Automation to
create the Name objects in each EXCEL workbook while the file is open, and
use the collection to store the names of the Name objects. Then you'd need
to save the EXCEL file before or as part of closing the file so that the
Name objects can be read by ACCESS during the TransferSpreadsheet action.

So, in order to progress on this, let me know what you want to be the
process for this setup, and then we can put together some code.
--

Ken Snell
http://www.accessmvp.com/KDSnell/

This morning, it was looking at my C drive for the path then I noticed I
needed to change the strFilename to strPath in the
DoCmd.TransferSpreadsheet
line.

Now with adding the .Address to the .UsedRange it is getting closer. The
first sheet name that it should bring in is '01) 22_24 LTY' and the range
is
$A$1:$I$26. The error now is:

Run-time error '3011':
The Microsoft Office Access database engine could not find the object
''01]
22_24 LTY'$$A41:$I$26'. Make sure the object exists and that you spell its
name and the path name correctly.

Debug takes me to the DoCmd.TransferSpreadsheet line item.
Immediate window shows correct path and sheet name.

I thought maybe it was the ) in the sheet name since it is a bracket in
the
error, but it is correct in the immediate window. If I change it to
something
else, I get the same error but with the new character in place of the
bracket.

Can you think of anything that would cause this? It just baffles me b/c
the
immediate window shows the right thing. I thought maybe it was a problem
that
the workbook is shared and the sheets are protected, but if that was a
problem then it wouldn't have shown the correct range to begin with.

Thanks!
KCCC

Sorry... typo on my part.... not used to using .UsedRange property from
EXCEL.

Corrected code block:

'Open the Excel file and read the worksheet names into a collection in
Access
Set colWorksheets = New Collection
'****next step is new
Set colWkshtUsedRange = New Collection
Set objWorkbook = objExcel.Workbooks.Open(strPath, , blnReadOnly, ,
strPassword)
intNoOfSheets = objWorkbook.Worksheets.Count
For lngCount = lngStartSheet To lngEndSheet
colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
****next step is new -- REVISED to add .Address to .UsedRange
colWkshtUsedRange.Add
objWorkbook.Worksheets(lngCount).UsedRange.Address
Next lngCount

--

Ken Snell
http://www.accessmvp.com/KDSnell/


message


.

 

[Ken Snell]

By the way, I see that Microsoft plans to shut down this newsgroup on June
1. I have tried without any appreciable success to use the "new" web-based
forum that Microsoft thinks everyone should use. I can't find posts there
that I know are there, including ones I've posted. So I refuse to try to use
the forums.

As such, if we don't finish this thread before Microsoft shuts down this
group, you can contact me via my website (see my signature) and we can start
a new thread in comp.databases.ms-access newsgroup (I am hopeful that you
can gain or have access to that newsgroup?) in order to complete this
thread!

 

[Ken Snell]

Wait a minute... it just hit me. You don't need to specify the cell address
for the UsedRange. When you specify the worksheet in TransferSpreadsheet,
ACCESS automatically uses the UsedRange on that worksheet to identify which
cells to read.

So just write your code to set strWorksheetName to this:

strWorksheetName = _
IIf(colWorksheets(lngWorksheetItem) Like "*'*" Or _
colWorksheets(lngWorksheetItem) Like "* *", "'" & _
colWorksheets(lngWorksheetItem) & "'", _
colWorksheets(lngWorksheetItem)) & "$"


Does this work for you?
--

Ken Snell
http://www.accessmvp.com/KDSnell/

Well, I've done some experimenting tonite on this issue because I don't
have much experience with trying to import a specific cell block from a
specific worksheet. Your setup fails for me, too.

I think I have the reason for why it fails. This is the explanation for
the Range argument from Help file:

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.


Based on the above, you can only specify
(1) a range that is defined in the workbook; or

(2) a worksheet name (followed by $) because a worksheet is interpreted as
a Range when you do this, and EXCEL automatically assigns a default Range
to a worksheet name; or

(3) a specific block of cells.


But you cannot specify a worksheet name AND a block of cells. Bummer.

What you would need is a defined Name (Range) for the specific cells on
the specific worksheet to be in the workbook. My guess, though, is that
this range of cells will be different each time? and that you would not
want to manually create the Name in each workbook for each worksheet's
UsedRange before you run your code?

Assuming that you want this to be automated, you'd need to use Automation
to create the Name objects in each EXCEL workbook while the file is open,
and use the collection to store the names of the Name objects. Then you'd
need to save the EXCEL file before or as part of closing the file so that
the Name objects can be read by ACCESS during the TransferSpreadsheet
action.

So, in order to progress on this, let me know what you want to be the
process for this setup, and then we can put together some code.
--

Ken Snell
http://www.accessmvp.com/KDSnell/

This morning, it was looking at my C drive for the path then I noticed I
needed to change the strFilename to strPath in the
DoCmd.TransferSpreadsheet
line.

Now with adding the .Address to the .UsedRange it is getting closer. The
first sheet name that it should bring in is '01) 22_24 LTY' and the range
is
$A$1:$I$26. The error now is:

Run-time error '3011':
The Microsoft Office Access database engine could not find the object
''01]
22_24 LTY'$$A41:$I$26'. Make sure the object exists and that you spell
its
name and the path name correctly.

Debug takes me to the DoCmd.TransferSpreadsheet line item.
Immediate window shows correct path and sheet name.

I thought maybe it was the ) in the sheet name since it is a bracket in
the
error, but it is correct in the immediate window. If I change it to
something
else, I get the same error but with the new character in place of the
bracket.

Can you think of anything that would cause this? It just baffles me b/c
the
immediate window shows the right thing. I thought maybe it was a problem
that
the workbook is shared and the sheets are protected, but if that was a
problem then it wouldn't have shown the correct range to begin with.

Thanks!
KCCC

Sorry... typo on my part.... not used to using .UsedRange property from
EXCEL.

Corrected code block:

'Open the Excel file and read the worksheet names into a collection
in
Access
Set colWorksheets = New Collection
'****next step is new
Set colWkshtUsedRange = New Collection
Set objWorkbook = objExcel.Workbooks.Open(strPath, , blnReadOnly, ,
strPassword)
intNoOfSheets = objWorkbook.Worksheets.Count
For lngCount = lngStartSheet To lngEndSheet
colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
****next step is new -- REVISED to add .Address to .UsedRange
colWkshtUsedRange.Add
objWorkbook.Worksheets(lngCount).UsedRange.Address
Next lngCount

--

Ken Snell
http://www.accessmvp.com/KDSnell/


message
Ken,

After I typed it all in correctly, I am getting "Error 424. Object
Required." When I debug, it highlights this portion, pointing to the
"colWkshtUsedRange(lngWorksheetItem)" line:

strWorksheetName = _
IIf(colWorksheets(lngWorksheetItem) Like "*'*" Or _
colWorksheets(lngWorksheetItem) Like "* *", "'" & _
colWorksheets(lngWorksheetItem) & "'", _
colWorksheets(lngWorksheetItem)) & "!" & _
colWkshtUsedRange(lngWorksheetItem)

Any other suggestions?

Thanks!



:

OK. Let's see what we can do to help -- watch for wrapping of the
code
lines
by the newsreader:

Let's add these steps to your "dim" code block:
--------------------
Dim colWkshtUsedRange As Collection
Dim lngWorksheetItem As Integer

To this code block, I've added steps "delimited" by comment steps:
--------------------
'Open the Excel file and read the worksheet names into a
collection in
Access
Set colWorksheets = New Collection
'****next step is new
Set colWkshtUsedRange = New Collection
Set objWorkbook = objExcel.Workbooks.Open(strPath, , blnReadOnly,
,
strPassword)
intNoOfSheets = objWorkbook.Worksheets.Count
For lngCount = lngStartSheet To lngEndSheet
colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
****next step is new
colWkshtUsedRange.Add
objWorkbook.Worksheets(lngCount).UsedRange
Next lngCount


Now, modify this code block to what I show below:
--------------------------------
'Import the data from each worksheet into the table
For Each objWorksheet In colWorksheets
Set objRange = objWorksheet.UsedRange
strWorksheetName = objWorksheet.Name & "!" &
objRange.Address(False,
False)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12,
"tblAllRuns", strFilename, True, strWorksheetName
Next
'Delete the collection from Access
Set colWorksheets = Nothing

'*****replacement code for above block:
'Import the data from each worksheet into the table
For lngWorksheetItem = 1 To colWorksheets.Count
strWorksheetName = _
IIf(colWorksheets(lngWorksheetItem) Like "*'*" Or _
colWorksheets(lngWorksheetItem) Like "* *", "'" & _
colWorksheets(lngWorksheetItem) & "'", _
colWorksheets(lngWorksheetItem)) & "!" & _
colWkshtUsedRange(lngWorksheetItem)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12,
"tblAllRuns", strFilename, True, strWorksheetName
Next lngWorksheetItem
'Delete the collection from Access
Set colWorksheets = Nothing
Set colWkshtUsedRange = Nothing

--

Ken Snell
http://www.accessmvp.com/KDSnell/



.

 

[Ken Snell]

Slight change to code:

strWorksheetName = _
IIf(colWorksheets(lngWorksheetItem) Like "*'*" Or _
colWorksheets(lngWorksheetItem) Like "* *", "'" & _
Replace(colWorksheets(lngWorksheetItem), "'", "''") & "'", _
colWorksheets(lngWorksheetItem)) & "$"

--

Ken Snell
http://www.accessmvp.com/KDSnell/

Wait a minute... it just hit me. You don't need to specify the cell
address for the UsedRange. When you specify the worksheet in
TransferSpreadsheet, ACCESS automatically uses the UsedRange on that
worksheet to identify which cells to read.

So just write your code to set strWorksheetName to this:

strWorksheetName = _
IIf(colWorksheets(lngWorksheetItem) Like "*'*" Or _
colWorksheets(lngWorksheetItem) Like "* *", "'" & _
colWorksheets(lngWorksheetItem) & "'", _
colWorksheets(lngWorksheetItem)) & "$"


Does this work for you?
--

Ken Snell
http://www.accessmvp.com/KDSnell/

Well, I've done some experimenting tonite on this issue because I don't
have much experience with trying to import a specific cell block from a
specific worksheet. Your setup fails for me, too.

I think I have the reason for why it fails. This is the explanation for
the Range argument from Help file:

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.


Based on the above, you can only specify
(1) a range that is defined in the workbook; or

(2) a worksheet name (followed by $) because a worksheet is interpreted
as a Range when you do this, and EXCEL automatically assigns a default
Range to a worksheet name; or

(3) a specific block of cells.


But you cannot specify a worksheet name AND a block of cells. Bummer.

What you would need is a defined Name (Range) for the specific cells on
the specific worksheet to be in the workbook. My guess, though, is that
this range of cells will be different each time? and that you would not
want to manually create the Name in each workbook for each worksheet's
UsedRange before you run your code?

Assuming that you want this to be automated, you'd need to use Automation
to create the Name objects in each EXCEL workbook while the file is open,
and use the collection to store the names of the Name objects. Then you'd
need to save the EXCEL file before or as part of closing the file so that
the Name objects can be read by ACCESS during the TransferSpreadsheet
action.

So, in order to progress on this, let me know what you want to be the
process for this setup, and then we can put together some code.
--

Ken Snell
http://www.accessmvp.com/KDSnell/

This morning, it was looking at my C drive for the path then I noticed I
needed to change the strFilename to strPath in the
DoCmd.TransferSpreadsheet
line.

Now with adding the .Address to the .UsedRange it is getting closer. The
first sheet name that it should bring in is '01) 22_24 LTY' and the
range is
$A$1:$I$26. The error now is:

Run-time error '3011':
The Microsoft Office Access database engine could not find the object
''01]
22_24 LTY'$$A41:$I$26'. Make sure the object exists and that you spell
its
name and the path name correctly.

Debug takes me to the DoCmd.TransferSpreadsheet line item.
Immediate window shows correct path and sheet name.

I thought maybe it was the ) in the sheet name since it is a bracket in
the
error, but it is correct in the immediate window. If I change it to
something
else, I get the same error but with the new character in place of the
bracket.

Can you think of anything that would cause this? It just baffles me b/c
the
immediate window shows the right thing. I thought maybe it was a problem
that
the workbook is shared and the sheets are protected, but if that was a
problem then it wouldn't have shown the correct range to begin with.

Thanks!
KCCC


:

Sorry... typo on my part.... not used to using .UsedRange property from
EXCEL.

Corrected code block:

'Open the Excel file and read the worksheet names into a collection
in
Access
Set colWorksheets = New Collection
'****next step is new
Set colWkshtUsedRange = New Collection
Set objWorkbook = objExcel.Workbooks.Open(strPath, , blnReadOnly, ,
strPassword)
intNoOfSheets = objWorkbook.Worksheets.Count
For lngCount = lngStartSheet To lngEndSheet
colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
****next step is new -- REVISED to add .Address to .UsedRange
colWkshtUsedRange.Add
objWorkbook.Worksheets(lngCount).UsedRange.Address
Next lngCount

--

Ken Snell
http://www.accessmvp.com/KDSnell/


message
Ken,

After I typed it all in correctly, I am getting "Error 424. Object
Required." When I debug, it highlights this portion, pointing to the
"colWkshtUsedRange(lngWorksheetItem)" line:

strWorksheetName = _
IIf(colWorksheets(lngWorksheetItem) Like "*'*" Or _
colWorksheets(lngWorksheetItem) Like "* *", "'" & _
colWorksheets(lngWorksheetItem) & "'", _
colWorksheets(lngWorksheetItem)) & "!" & _
colWkshtUsedRange(lngWorksheetItem)

Any other suggestions?

Thanks!



:

OK. Let's see what we can do to help -- watch for wrapping of the
code
lines
by the newsreader:

Let's add these steps to your "dim" code block:
--------------------
Dim colWkshtUsedRange As Collection
Dim lngWorksheetItem As Integer

To this code block, I've added steps "delimited" by comment steps:
--------------------
'Open the Excel file and read the worksheet names into a
collection in
Access
Set colWorksheets = New Collection
'****next step is new
Set colWkshtUsedRange = New Collection
Set objWorkbook = objExcel.Workbooks.Open(strPath, , blnReadOnly,
,
strPassword)
intNoOfSheets = objWorkbook.Worksheets.Count
For lngCount = lngStartSheet To lngEndSheet
colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
****next step is new
colWkshtUsedRange.Add
objWorkbook.Worksheets(lngCount).UsedRange
Next lngCount


Now, modify this code block to what I show below:
--------------------------------
'Import the data from each worksheet into the table
For Each objWorksheet In colWorksheets
Set objRange = objWorksheet.UsedRange
strWorksheetName = objWorksheet.Name & "!" &
objRange.Address(False,
False)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12,
"tblAllRuns", strFilename, True, strWorksheetName
Next
'Delete the collection from Access
Set colWorksheets = Nothing

'*****replacement code for above block:
'Import the data from each worksheet into the table
For lngWorksheetItem = 1 To colWorksheets.Count
strWorksheetName = _
IIf(colWorksheets(lngWorksheetItem) Like "*'*" Or _
colWorksheets(lngWorksheetItem) Like "* *", "'" & _
colWorksheets(lngWorksheetItem) & "'", _
colWorksheets(lngWorksheetItem)) & "!" & _
colWkshtUsedRange(lngWorksheetItem)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12,
"tblAllRuns", strFilename, True, strWorksheetName
Next lngWorksheetItem
'Delete the collection from Access
Set colWorksheets = Nothing
Set colWkshtUsedRange = Nothing

--

Ken Snell
http://www.accessmvp.com/KDSnell/



.

 

[KC_Cheer_Coach]

Great! I am trying right now.

And yes, you are correct. In my old db, I have several macros that pull in
every worksheet and each range is different. When you have 12 workbooks and
each one has between 31-46 tabs, it really stinks when someone wants to make
a change to ONE line item that affects about a third of the sheets in 7 of
the workbooks. Automation is the key in this one.

I will let you know how this turns out. I really appreciate all the help you
have given me.

KCCC

Slight change to code:

strWorksheetName = _
IIf(colWorksheets(lngWorksheetItem) Like "*'*" Or _
colWorksheets(lngWorksheetItem) Like "* *", "'" & _
Replace(colWorksheets(lngWorksheetItem), "'", "''") & "'", _
colWorksheets(lngWorksheetItem)) & "$"

--

Ken Snell
http://www.accessmvp.com/KDSnell/

Wait a minute... it just hit me. You don't need to specify the cell
address for the UsedRange. When you specify the worksheet in
TransferSpreadsheet, ACCESS automatically uses the UsedRange on that
worksheet to identify which cells to read.

So just write your code to set strWorksheetName to this:

strWorksheetName = _
IIf(colWorksheets(lngWorksheetItem) Like "*'*" Or _
colWorksheets(lngWorksheetItem) Like "* *", "'" & _
colWorksheets(lngWorksheetItem) & "'", _
colWorksheets(lngWorksheetItem)) & "$"


Does this work for you?
--

Ken Snell
http://www.accessmvp.com/KDSnell/

Well, I've done some experimenting tonite on this issue because I don't
have much experience with trying to import a specific cell block from a
specific worksheet. Your setup fails for me, too.

I think I have the reason for why it fails. This is the explanation for
the Range argument from Help file:

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.


Based on the above, you can only specify
(1) a range that is defined in the workbook; or

(2) a worksheet name (followed by $) because a worksheet is interpreted
as a Range when you do this, and EXCEL automatically assigns a default
Range to a worksheet name; or

(3) a specific block of cells.


But you cannot specify a worksheet name AND a block of cells. Bummer.

What you would need is a defined Name (Range) for the specific cells on
the specific worksheet to be in the workbook. My guess, though, is that
this range of cells will be different each time? and that you would not
want to manually create the Name in each workbook for each worksheet's
UsedRange before you run your code?

Assuming that you want this to be automated, you'd need to use Automation
to create the Name objects in each EXCEL workbook while the file is open,
and use the collection to store the names of the Name objects. Then you'd
need to save the EXCEL file before or as part of closing the file so that
the Name objects can be read by ACCESS during the TransferSpreadsheet
action.

So, in order to progress on this, let me know what you want to be the
process for this setup, and then we can put together some code.
--

Ken Snell
http://www.accessmvp.com/KDSnell/







message This morning, it was looking at my C drive for the path then I noticed I
needed to change the strFilename to strPath in the
DoCmd.TransferSpreadsheet
line.

Now with adding the .Address to the .UsedRange it is getting closer. The
first sheet name that it should bring in is '01) 22_24 LTY' and the
range is
$A$1:$I$26. The error now is:

Run-time error '3011':
The Microsoft Office Access database engine could not find the object
''01]
22_24 LTY'$$A41:$I$26'. Make sure the object exists and that you spell
its
name and the path name correctly.

Debug takes me to the DoCmd.TransferSpreadsheet line item.
Immediate window shows correct path and sheet name.

I thought maybe it was the ) in the sheet name since it is a bracket in
the
error, but it is correct in the immediate window. If I change it to
something
else, I get the same error but with the new character in place of the
bracket.

Can you think of anything that would cause this? It just baffles me b/c
the
immediate window shows the right thing. I thought maybe it was a problem
that
the workbook is shared and the sheets are protected, but if that was a
problem then it wouldn't have shown the correct range to begin with.

Thanks!
KCCC


:

Sorry... typo on my part.... not used to using .UsedRange property from
EXCEL.

Corrected code block:

'Open the Excel file and read the worksheet names into a collection
in
Access
Set colWorksheets = New Collection
'****next step is new
Set colWkshtUsedRange = New Collection
Set objWorkbook = objExcel.Workbooks.Open(strPath, , blnReadOnly, ,
strPassword)
intNoOfSheets = objWorkbook.Worksheets.Count
For lngCount = lngStartSheet To lngEndSheet
colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
****next step is new -- REVISED to add .Address to .UsedRange
colWkshtUsedRange.Add
objWorkbook.Worksheets(lngCount).UsedRange.Address
Next lngCount

--

Ken Snell
http://www.accessmvp.com/KDSnell/


message
Ken,

After I typed it all in correctly, I am getting "Error 424. Object
Required." When I debug, it highlights this portion, pointing to the
"colWkshtUsedRange(lngWorksheetItem)" line:

strWorksheetName = _
IIf(colWorksheets(lngWorksheetItem) Like "*'*" Or _
colWorksheets(lngWorksheetItem) Like "* *", "'" & _
colWorksheets(lngWorksheetItem) & "'", _
colWorksheets(lngWorksheetItem)) & "!" & _
colWkshtUsedRange(lngWorksheetItem)

Any other suggestions?

Thanks!



:

OK. Let's see what we can do to help -- watch for wrapping of the
code
lines
by the newsreader:

Let's add these steps to your "dim" code block:
--------------------
Dim colWkshtUsedRange As Collection
Dim lngWorksheetItem As Integer

To this code block, I've added steps "delimited" by comment steps:
--------------------
'Open the Excel file and read the worksheet names into a
collection in
Access
Set colWorksheets = New Collection
'****next step is new
Set colWkshtUsedRange = New Collection
Set objWorkbook = objExcel.Workbooks.Open(strPath, , blnReadOnly,
,
strPassword)
intNoOfSheets = objWorkbook.Worksheets.Count
For lngCount = lngStartSheet To lngEndSheet
colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
****next step is new
colWkshtUsedRange.Add
objWorkbook.Worksheets(lngCount).UsedRange
Next lngCount


Now, modify this code block to what I show below:
--------------------------------
'Import the data from each worksheet into the table
For Each objWorksheet In colWorksheets
Set objRange = objWorksheet.UsedRange
strWorksheetName = objWorksheet.Name & "!" &
objRange.Address(False,
False)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12,
"tblAllRuns", strFilename, True, strWorksheetName
Next
'Delete the collection from Access
Set colWorksheets = Nothing

'*****replacement code for above block:
'Import the data from each worksheet into the table
For lngWorksheetItem = 1 To colWorksheets.Count
strWorksheetName = _
IIf(colWorksheets(lngWorksheetItem) Like "*'*" Or _
colWorksheets(lngWorksheetItem) Like "* *", "'" & _
colWorksheets(lngWorksheetItem) & "'", _
colWorksheets(lngWorksheetItem)) & "!" & _
colWkshtUsedRange(lngWorksheetItem)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12,
"tblAllRuns", strFilename, True, strWorksheetName
Next lngWorksheetItem
'Delete the collection from Access
Set colWorksheets = Nothing
Set colWkshtUsedRange = Nothing

--

Ken Snell
http://www.accessmvp.com/KDSnell/



.

.

 

[KC_Cheer_Coach]

As is:
Run-time error ‘3125’: ‘‘01] 22_24 LTY’$’ is not a valid name. Make sure
that it does not include invalid characters or punctuation and that it is not
too long.
Debug = DoCmd.TransferSpreadsheet line item.
Immediate window shows:
?strWorksheetName
'01) 22_24 LTY'$

If I add:
& colWkshtUsedRange(lngWorksheetItem) to the end the error is:
Run-time error ‘3011’: The Microsoft Office Access database engine could not
find the object ‘‘01] 22_24 LTY’$$A$1:$I$26’. Make sure the object exists
and that you spell its name and path name correctly.
Debug = DoCmd.TransferSpreadsheet line item.
Immediate window shows:
?strWorksheetName
'01) 22_24 LTY'$$A$1:$I$26

This is all I have to work on all day today, so I will keep messing with it
and let you know if I figure something out...unless you come up with another
suggestion.

KCCC

Slight change to code:

strWorksheetName = _
IIf(colWorksheets(lngWorksheetItem) Like "*'*" Or _
colWorksheets(lngWorksheetItem) Like "* *", "'" & _
Replace(colWorksheets(lngWorksheetItem), "'", "''") & "'", _
colWorksheets(lngWorksheetItem)) & "$"

--

Ken Snell
http://www.accessmvp.com/KDSnell/

Wait a minute... it just hit me. You don't need to specify the cell
address for the UsedRange. When you specify the worksheet in
TransferSpreadsheet, ACCESS automatically uses the UsedRange on that
worksheet to identify which cells to read.

So just write your code to set strWorksheetName to this:

strWorksheetName = _
IIf(colWorksheets(lngWorksheetItem) Like "*'*" Or _
colWorksheets(lngWorksheetItem) Like "* *", "'" & _
colWorksheets(lngWorksheetItem) & "'", _
colWorksheets(lngWorksheetItem)) & "$"


Does this work for you?
--

Ken Snell
http://www.accessmvp.com/KDSnell/

Well, I've done some experimenting tonite on this issue because I don't
have much experience with trying to import a specific cell block from a
specific worksheet. Your setup fails for me, too.

I think I have the reason for why it fails. This is the explanation for
the Range argument from Help file:

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.


Based on the above, you can only specify
(1) a range that is defined in the workbook; or

(2) a worksheet name (followed by $) because a worksheet is interpreted
as a Range when you do this, and EXCEL automatically assigns a default
Range to a worksheet name; or

(3) a specific block of cells.


But you cannot specify a worksheet name AND a block of cells. Bummer.

What you would need is a defined Name (Range) for the specific cells on
the specific worksheet to be in the workbook. My guess, though, is that
this range of cells will be different each time? and that you would not
want to manually create the Name in each workbook for each worksheet's
UsedRange before you run your code?

Assuming that you want this to be automated, you'd need to use Automation
to create the Name objects in each EXCEL workbook while the file is open,
and use the collection to store the names of the Name objects. Then you'd
need to save the EXCEL file before or as part of closing the file so that
the Name objects can be read by ACCESS during the TransferSpreadsheet
action.

So, in order to progress on this, let me know what you want to be the
process for this setup, and then we can put together some code.
--

Ken Snell
http://www.accessmvp.com/KDSnell/







message This morning, it was looking at my C drive for the path then I noticed I
needed to change the strFilename to strPath in the
DoCmd.TransferSpreadsheet
line.

Now with adding the .Address to the .UsedRange it is getting closer. The
first sheet name that it should bring in is '01) 22_24 LTY' and the
range is
$A$1:$I$26. The error now is:

Run-time error '3011':
The Microsoft Office Access database engine could not find the object
''01]
22_24 LTY'$$A41:$I$26'. Make sure the object exists and that you spell
its
name and the path name correctly.

Debug takes me to the DoCmd.TransferSpreadsheet line item.
Immediate window shows correct path and sheet name.

I thought maybe it was the ) in the sheet name since it is a bracket in
the
error, but it is correct in the immediate window. If I change it to
something
else, I get the same error but with the new character in place of the
bracket.

Can you think of anything that would cause this? It just baffles me b/c
the
immediate window shows the right thing. I thought maybe it was a problem
that
the workbook is shared and the sheets are protected, but if that was a
problem then it wouldn't have shown the correct range to begin with.

Thanks!
KCCC


:

Sorry... typo on my part.... not used to using .UsedRange property from
EXCEL.

Corrected code block:

'Open the Excel file and read the worksheet names into a collection
in
Access
Set colWorksheets = New Collection
'****next step is new
Set colWkshtUsedRange = New Collection
Set objWorkbook = objExcel.Workbooks.Open(strPath, , blnReadOnly, ,
strPassword)
intNoOfSheets = objWorkbook.Worksheets.Count
For lngCount = lngStartSheet To lngEndSheet
colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
****next step is new -- REVISED to add .Address to .UsedRange
colWkshtUsedRange.Add
objWorkbook.Worksheets(lngCount).UsedRange.Address
Next lngCount

--

Ken Snell
http://www.accessmvp.com/KDSnell/


message
Ken,

After I typed it all in correctly, I am getting "Error 424. Object
Required." When I debug, it highlights this portion, pointing to the
"colWkshtUsedRange(lngWorksheetItem)" line:

strWorksheetName = _
IIf(colWorksheets(lngWorksheetItem) Like "*'*" Or _
colWorksheets(lngWorksheetItem) Like "* *", "'" & _
colWorksheets(lngWorksheetItem) & "'", _
colWorksheets(lngWorksheetItem)) & "!" & _
colWkshtUsedRange(lngWorksheetItem)

Any other suggestions?

Thanks!



:

OK. Let's see what we can do to help -- watch for wrapping of the
code
lines
by the newsreader:

Let's add these steps to your "dim" code block:
--------------------
Dim colWkshtUsedRange As Collection
Dim lngWorksheetItem As Integer

To this code block, I've added steps "delimited" by comment steps:
--------------------
'Open the Excel file and read the worksheet names into a
collection in
Access
Set colWorksheets = New Collection
'****next step is new
Set colWkshtUsedRange = New Collection
Set objWorkbook = objExcel.Workbooks.Open(strPath, , blnReadOnly,
,
strPassword)
intNoOfSheets = objWorkbook.Worksheets.Count
For lngCount = lngStartSheet To lngEndSheet
colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
****next step is new
colWkshtUsedRange.Add
objWorkbook.Worksheets(lngCount).UsedRange
Next lngCount


Now, modify this code block to what I show below:
--------------------------------
'Import the data from each worksheet into the table
For Each objWorksheet In colWorksheets
Set objRange = objWorksheet.UsedRange
strWorksheetName = objWorksheet.Name & "!" &
objRange.Address(False,
False)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12,
"tblAllRuns", strFilename, True, strWorksheetName
Next
'Delete the collection from Access
Set colWorksheets = Nothing

'*****replacement code for above block:
'Import the data from each worksheet into the table
For lngWorksheetItem = 1 To colWorksheets.Count
strWorksheetName = _
IIf(colWorksheets(lngWorksheetItem) Like "*'*" Or _
colWorksheets(lngWorksheetItem) Like "* *", "'" & _
colWorksheets(lngWorksheetItem) & "'", _
colWorksheets(lngWorksheetItem)) & "!" & _
colWkshtUsedRange(lngWorksheetItem)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12,
"tblAllRuns", strFilename, True, strWorksheetName
Next lngWorksheetItem
'Delete the collection from Access
Set colWorksheets = Nothing
Set colWkshtUsedRange = Nothing

--

Ken Snell
http://www.accessmvp.com/KDSnell/



.

.

 

[KC_Cheer_Coach]

KEN!

The last piece of code you suggested works. The problem was my workbook, but
the protected sheets and shared workbook didn't have anything to do with it.
When I renamed every single sheet to something that didn't have a ")" or "_"
or space in it, it ran. It doesn't bother me that I had to take that stuff
out. It makes the tabs look cleaner anyway.

I still have some work to do with it since it brought in every tab except
the last three on the test workbook (grrrr).

I will mark your last posting as good. I appreciate your help so much!

Have a great weekend!

KCCC

Slight change to code:

strWorksheetName = _
IIf(colWorksheets(lngWorksheetItem) Like "*'*" Or _
colWorksheets(lngWorksheetItem) Like "* *", "'" & _
Replace(colWorksheets(lngWorksheetItem), "'", "''") & "'", _
colWorksheets(lngWorksheetItem)) & "$"

--

Ken Snell
http://www.accessmvp.com/KDSnell/

Wait a minute... it just hit me. You don't need to specify the cell
address for the UsedRange. When you specify the worksheet in
TransferSpreadsheet, ACCESS automatically uses the UsedRange on that
worksheet to identify which cells to read.

So just write your code to set strWorksheetName to this:

strWorksheetName = _
IIf(colWorksheets(lngWorksheetItem) Like "*'*" Or _
colWorksheets(lngWorksheetItem) Like "* *", "'" & _
colWorksheets(lngWorksheetItem) & "'", _
colWorksheets(lngWorksheetItem)) & "$"


Does this work for you?
--

Ken Snell
http://www.accessmvp.com/KDSnell/

Well, I've done some experimenting tonite on this issue because I don't
have much experience with trying to import a specific cell block from a
specific worksheet. Your setup fails for me, too.

I think I have the reason for why it fails. This is the explanation for
the Range argument from Help file:

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.


Based on the above, you can only specify
(1) a range that is defined in the workbook; or

(2) a worksheet name (followed by $) because a worksheet is interpreted
as a Range when you do this, and EXCEL automatically assigns a default
Range to a worksheet name; or

(3) a specific block of cells.


But you cannot specify a worksheet name AND a block of cells. Bummer.

What you would need is a defined Name (Range) for the specific cells on
the specific worksheet to be in the workbook. My guess, though, is that
this range of cells will be different each time? and that you would not
want to manually create the Name in each workbook for each worksheet's
UsedRange before you run your code?

Assuming that you want this to be automated, you'd need to use Automation
to create the Name objects in each EXCEL workbook while the file is open,
and use the collection to store the names of the Name objects. Then you'd
need to save the EXCEL file before or as part of closing the file so that
the Name objects can be read by ACCESS during the TransferSpreadsheet
action.

So, in order to progress on this, let me know what you want to be the
process for this setup, and then we can put together some code.
--

Ken Snell
http://www.accessmvp.com/KDSnell/







message This morning, it was looking at my C drive for the path then I noticed I
needed to change the strFilename to strPath in the
DoCmd.TransferSpreadsheet
line.

Now with adding the .Address to the .UsedRange it is getting closer. The
first sheet name that it should bring in is '01) 22_24 LTY' and the
range is
$A$1:$I$26. The error now is:

Run-time error '3011':
The Microsoft Office Access database engine could not find the object
''01]
22_24 LTY'$$A41:$I$26'. Make sure the object exists and that you spell
its
name and the path name correctly.

Debug takes me to the DoCmd.TransferSpreadsheet line item.
Immediate window shows correct path and sheet name.

I thought maybe it was the ) in the sheet name since it is a bracket in
the
error, but it is correct in the immediate window. If I change it to
something
else, I get the same error but with the new character in place of the
bracket.

Can you think of anything that would cause this? It just baffles me b/c
the
immediate window shows the right thing. I thought maybe it was a problem
that
the workbook is shared and the sheets are protected, but if that was a
problem then it wouldn't have shown the correct range to begin with.

Thanks!
KCCC


:

Sorry... typo on my part.... not used to using .UsedRange property from
EXCEL.

Corrected code block:

'Open the Excel file and read the worksheet names into a collection
in
Access
Set colWorksheets = New Collection
'****next step is new
Set colWkshtUsedRange = New Collection
Set objWorkbook = objExcel.Workbooks.Open(strPath, , blnReadOnly, ,
strPassword)
intNoOfSheets = objWorkbook.Worksheets.Count
For lngCount = lngStartSheet To lngEndSheet
colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
****next step is new -- REVISED to add .Address to .UsedRange
colWkshtUsedRange.Add
objWorkbook.Worksheets(lngCount).UsedRange.Address
Next lngCount

--

Ken Snell
http://www.accessmvp.com/KDSnell/


message
Ken,

After I typed it all in correctly, I am getting "Error 424. Object
Required." When I debug, it highlights this portion, pointing to the
"colWkshtUsedRange(lngWorksheetItem)" line:

strWorksheetName = _
IIf(colWorksheets(lngWorksheetItem) Like "*'*" Or _
colWorksheets(lngWorksheetItem) Like "* *", "'" & _
colWorksheets(lngWorksheetItem) & "'", _
colWorksheets(lngWorksheetItem)) & "!" & _
colWkshtUsedRange(lngWorksheetItem)

Any other suggestions?

Thanks!



:

OK. Let's see what we can do to help -- watch for wrapping of the
code
lines
by the newsreader:

Let's add these steps to your "dim" code block:
--------------------
Dim colWkshtUsedRange As Collection
Dim lngWorksheetItem As Integer

To this code block, I've added steps "delimited" by comment steps:
--------------------
'Open the Excel file and read the worksheet names into a
collection in
Access
Set colWorksheets = New Collection
'****next step is new
Set colWkshtUsedRange = New Collection
Set objWorkbook = objExcel.Workbooks.Open(strPath, , blnReadOnly,
,
strPassword)
intNoOfSheets = objWorkbook.Worksheets.Count
For lngCount = lngStartSheet To lngEndSheet
colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
****next step is new
colWkshtUsedRange.Add
objWorkbook.Worksheets(lngCount).UsedRange
Next lngCount


Now, modify this code block to what I show below:
--------------------------------
'Import the data from each worksheet into the table
For Each objWorksheet In colWorksheets
Set objRange = objWorksheet.UsedRange
strWorksheetName = objWorksheet.Name & "!" &
objRange.Address(False,
False)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12,
"tblAllRuns", strFilename, True, strWorksheetName
Next
'Delete the collection from Access
Set colWorksheets = Nothing

'*****replacement code for above block:
'Import the data from each worksheet into the table
For lngWorksheetItem = 1 To colWorksheets.Count
strWorksheetName = _
IIf(colWorksheets(lngWorksheetItem) Like "*'*" Or _
colWorksheets(lngWorksheetItem) Like "* *", "'" & _
colWorksheets(lngWorksheetItem) & "'", _
colWorksheets(lngWorksheetItem)) & "!" & _
colWkshtUsedRange(lngWorksheetItem)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12,
"tblAllRuns", strFilename, True, strWorksheetName
Next lngWorksheetItem
'Delete the collection from Access
Set colWorksheets = Nothing
Set colWkshtUsedRange = Nothing

--

Ken Snell
http://www.accessmvp.com/KDSnell/



.

.

 

[Ken Snell]

Success is always a good thing!
Good luck.