CODEWITHSUNDEEP
sqlexcelms-accessvba
user3049991
user3049991

Reputation: 41

Importing separate tabs of Excel spreadsheets into separate tables of MS Access database

I have a table called Pathmap with a column Path which is a list of filepaths of files I want to import

I then run the following code to try and import all these files into two tables in my database. One sheet called sts from each file goes into table1, and another sheet from within each file called CP goes into table2.

Sub pulloop()
DoCmd.RunSQL "delete * from table1"
DoCmd.RunSQL "delete * from table2"
strSql = "PathMap"
Set rs = CurrentDb.OpenRecordset(strSql)


With rs
If Not .BOF And Not .EOF Then
.MoveLast
.MoveFirst
    While (Not .EOF)
    importfile = rs.Fields("Path")
    objAccess.NewCurrentDatabase "Nate"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "TABLE1", importfile, true, STS
   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "TABLE2", importfile, True, CP
   'Debug.Print rs.Fields("Path")
    .MoveNext
    Wend
    End If
.Close
End With
End Sub

Problem is that it debugs, telling me that a field I know is only in the STS sheet of my importfiles, does not exist in destination Table2. It doesn't seem to actually be pulling from the CP sheet of the import file, but the STS one, and I am not sure what is wrong with my syntax here.

Help greatly appreciated. Difficulty with different tabs to different tables via docmd. transferspreadhseet.

Upvotes: 0

Views: 327

Answers (1)

Conrad Frix
Conrad Frix

Reputation: 52645

Based on the behavior described

this 

DoCmd.TransferSpreadsheet acImport, _
                          acSpreadsheetTypeExcel12, _
                         "TABLE2", _
                          importfile, _
                          true, _
                          CP

is doing the same

DoCmd.TransferSpreadsheet acImport, _
                          acSpreadsheetTypeExcel12, _
                         "TABLE2", _
                          importfile, _
                          true, _
                          Empty

When you write CP without quotes you are creating a variable called CP that is of type variant and assigning it the value Empty.

When DoCmd.TransferSpreadsheet receives a value of Empty for the range parameter it assumes you want the first spreadsheet. This is why Access is telling you that fields in STS don't exist in table2.

The way to fix this is to give the range value you want 

DoCmd.TransferSpreadsheet acImport, _
                          acSpreadsheetTypeExcel12, _
                         "TABLE2", _
                          importfile, _
                          true, _
                          "CP!"

I recommend you also add Option Explicit to the top of your module so that you don't accidentally make these mistakes later. Of course this does mean you'll need to declare rs and strSql.

Upvotes: 1

Related Questions