Import Multiple Workbooks & Worksheets based on Worksheets Name 1

[BigFish69]

I am trying to modify the following code from "Ken's Examples for Importing from EXCEL Workbook Files" in an Access 2007 database.

I need to cycle thru all the workbooks in c:\temp , open them and determine if any worksheets are name *child* "look for the word child which will have chars before and after the word and import only the worksheets that have the word child in them to an existing table in Access 2007. I would also like to find the worksheets that have the name *parent" in them and export those to another existing table in Access 2007. Not sure how to make this work correctly or if it can even be done.

Here's what I have so far but I get an error message saying the object does not support this property or method.

Thanks in advance for some direction!

Function ImportData()
Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean
Dim intWorkbookCounter As Integer
Dim lngCount As Long
Dim objExcel As Object, objWorkbook As Object
Dim colWorksheets As Collection
Dim strPath As String, strFile As String
Dim strPassword As String

' Establish an EXCEL application 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

blnHasFieldNames = True
strPath = "C:\temp\"
strPassword = vbNullString
blnReadOnly = True
strFile = Dir(strPath & "*.xls")
intWorkbookCounter = 0

Do While strFile <> ""

intWorkbookCounter = intWorkbookCounter + 1

Set colWorksheets = New Collection

Set objWorkbook = objExcel.Workbooks.Open(strPath & strFile, , _
blnReadOnly, , strPassword)

For lngCount = 1 To objWorkbook.Worksheets.Count
If objWorkbook.Worksheets.Name = "*child*" Then ' would like this line to look for the word child in the acutal worksheet name
colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
End If
Next lngCount

' Close the EXCEL file without saving the file, and clean up the EXCEL objects
objWorkbook.Close False
Set objWorkbook = Nothing

' Import the data from each worksheet into a separate table

For lngCount = colWorksheets.Count To 1 Step -1
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"tbl" & colWorksheets(lngCount) & intWorkbookCounter, _
strPath & strFile, blnHasFieldNames, _
colWorksheets(lngCount) & "$"
Next lngCount

' Delete the collection
Set colWorksheets = Nothing
strFile = Dir()

Loop

If blnEXCEL = True Then objExcel.Quit
Set objExcel = Nothing

End Function

 

[MazeWorX]

I think the error is occuring because of the * which represents a wildcard possibly as a work around look for a match without the *

something like

Dim strWorkSheet as String

    strWorkSheet = objWorkbook.Worksheets.Name
    strWorkSheet = Left(WorkSheet, 6)
    strWorkSheet = Right(WorkSheet, 5)
    If objWorkbook.Worksheets.Name = strWorkSheet Then

thoughts?

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[PHV]

Replace this:
If objWorkbook.Worksheets.Name = "*child*" Then
with this:
If objWorkbook.Worksheets(lngCount).Name Like "*child*" Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BigFish69]

I changed the worksheet name to a known name and reference it and still get the error message.

Any ideas?

 

[BigFish69]

PHV - I replaced the line with they one you posted and still get the object not supported message, it compile in the module fine.

Any thoughts?

 

[PHV]

Which line of code raises the error ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MazeWorX]

also have you checked your references?

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[MazeWorX]

Doh sorry after re reading your post I originally misunderstood. please disregard my original post thanks M

btw The code runs fine with PHV's suggestion



HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[BigFish69]

Sorry Guys! Had a water pipe bust on me last night.

The code does run but I can't seem to get this piece to work correctly. I have a couple of worksheets in the workbook called "childPT" and "childST" but they are not being added to the worksheets collection. No matter what the worksheet is named the code seems to always evaluate the If statement as false.

I've changed the name of the worksheet to "child" and tried that without success as well.

Any ideas?

For lngCount = 1 To objWorkbook.Worksheets.Count
If objWorkbook.Worksheets(lngCount).Name Like "*child*" Then
colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
Else
MsgBox "No Worksheets Added!" & vbCrLf & vbCrLf
End If


Next lngCount

 

[BigFish69]

I guess it can't be done! I'll have to link in all the worksheet names to the db and create a union query to pull them all together to make my child table.

Thanks for trying!!

 

[MazeWorX]

Tested with "childPT" and "childST" successfully. I cant seem to replicate your results. As PHV has asked which line of code is failing? Have you checked your references in the VBE?

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[BigFish69]

I just noticed the table name was actually "ChildPT" and "ChildST" instead of "childPT" and "childST". When I changed the lower case "c" to "C" all worked perfectly!

My mistake! Thanks guys for all your AWESOME support!