Need function to import Several Separate Workbook Sheets 1

[Fiat77]

I have a Database that has a Table named Tbl_Data.

Fields are

ID - AutoNumber Primary
Form ID - Number
Name of Submitter - Text
Originating Source - Text
Source ID - Text
Manager - text
Manager Usage Approved - Text
Contract Governance Person - Text
Contract Governance Approved - Text


These fields correspond exactly to the values in a Worksheet. The workbooks all contain a Worksheet named DBOutput. The range A2:H2 of the sheet DBOutput correlate perfectly to these fields. A2 = Form ID, B2 = Name of Submitter etc

I need a function that grabs every every Excel Workbook located in the folder C:\FirefighterForm
and takes the Excel Workook's sheet DBOutput Range A2:H2 and automatically appends it into Tbl_Data

How can I do this?

 

[SkipVought]

Hi,

In the Table View, just LINK to the Workbook/Sheet.

Then write an append query using the linked tables.

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[Fiat77]

Skip,
The problem is there are 500-600 of these Workbooks,and I was trying to see if I could do something with a loop to grab every Workbook's Sheet DBOutput!A2:H2 in the folder so I wouldn't have to do them one a ta time. Each file has a different name but they all have Sheet DBOutput!A2:H2

 

[VBslammer]

The secret here is to rename the fields in your table to the default values recognized by the TransferSpreadsheet action, i.e. "F1" for field 1, "F2" for field 2, etc.

Once you do that, you can loop through the workbooks and import the specified range without using an import specification:

Sub ImportFireFighterForms()
  Dim strPath As String
  Dim strFile As String
  
  strPath = "C:\FireFighterForm\"
  strFile = Dir(strPath & "*.xls")
  
  While strFile <> ""
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_Data", strPath & strFile, False, "DBOutput!A2:H2"
    strFile = Dir()
  Wend
End Sub

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[denosaur]

Something like this might also work. Note: you may need to change the .FileName line to match the naming conventions of the workbooks.

Sub ImportFireFighterForms()
Dim fName As String
Set fs = Application.FileSearch
With fs
    .LookIn = "C:\FireFighterForm"
    .FileName = "Tbl_Data*.xls"
    If .Execute(SortBy:=msoSortbyFileName, _
    SortOrder:=msoSortOrderAscending) > 0 Then
        For i = 1 To .FoundFiles.Count
            fName = .FoundFiles(i)
            MsgBox fName
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Tbl_Data", fName, True, "DBOutput!A2:H2"
        Next i
    Else
        MsgBox "There were no files found."
    End If
End With

End Sub

I hope that helps.

 

[VBslammer]

If you want to keep your table's field names, the spreadsheet needs to include the field names in the first row (maybe you already did this?):
[tt]
+---+-----------+-------------------+--------------------+----
| | A | B | C | ...
+---+-----------+-------------------+--------------------+----
| 1 | Form ID | Name of Submitter | Originating Source | ...
+---+-----------+-------------------+--------------------+----
| 2 | (Value) | (Value) | (Value) | ...
+---+-----------+-------------------+--------------------+----
[/tt]
If you want to do this, change the [tt]HasFieldNames[/tt] argument to [tt]True[/tt], and the Range to [tt]"DBOutput!A1:H2"[/tt]

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[Fiat77]

vbslammer thanks so much. My only question is this. What if my Sheet DBOutput has a non-contiguous range

ie

The data to import is DBOutput!(A2,b22,c22,f44)

What would I need to alter to get a range imported that was not all in for example row 2?

 

[Fiat77]

Also what happens on this if the Worksheet is Password protected and the Range is hidden. Can I set up something in the code to deal with this? Thanks

 

[Fiat77]

The Workbook might be Password Protected as well