Help with SQL statement for an excel import

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to use a loop to append data two columns at a time into an access
database from multiple sheets, the access table has 5 total fields, Location,
Value1, Value2, ExcelSheet, ColumnCount.

Location is always coming from the first column of each excel sheet
ExcelSheet is the name of the excel sheet the data is coming from
ColumnCount represents the iteration, column2 and 3 are 1, columns 4 and 5
are 2...

Below is the code so far. I believe I have everything set except for the
syntax of the strSQL. When I run this I get Object Required for dbD.Execute
strsql
Any Help?
 
Function ExcelImportResiduals()
Dim XLapp As Object
Dim XLFile As String
Dim XLSheet As String
Dim XLRange As String
Dim TableName As String
Dim z As Integer
Dim j As Integer
Dim SheetCount As Integer
Dim XX As Integer
Dim YY As Integer
Dim ColumnCount As Integer

Set XLapp = GetObject(, "Excel.Application")
XLapp.Visible = True
XLFile = "t:\Desktop\d\Monmouth\03.xls"
TableName = "ResidualData"
XLRange = "!"

Set XLwb = XLapp.Workbooks.Open(XLFile) 'Opens your file in Excel
SheetCount = XLapp.activeworkbook.Sheets.Count 'Gives you the total
number of Sheets
For z = 1 To SheetCount
XLSheet = XLapp.activeworkbook.Sheets(z).Name 'get name of sheet
number z
XLSheet = XLSheet & XLRange 'add range to
sheetname

For j = 2 To (XLapp.activeworkbook.Sheets(z).UsedRange.Columns.Count
- 1) Step 2
XX = j
YY = j + 1
ColumnCount = j / 2
strsql = "INSERT INTO " & TableName & " SELECT F1 As Location,
F" & XX & " As Value1, F" & YY & " As Value2, " & XLSheet & " As MYear, " &
ColumnCount & " As Day FROM [Excel 8.0;HDR=No;database=" & XLFile & ";].[" &
XLSheet & "]"
dbD.Execute strsql, dbFailOnError
Next j
Next z
MsgBox "Imported Successfully "
XLapp.Quit
Set XLapp = Nothing
Set XLwb = Nothing

End Function
 
Back
Top