No it is not working for me....what I am doing is first creating the table in vba
Code:
Sub CreateStagingTable()
Dim db As Database
Dim tbl As TableDef
Dim fld As Field
Set db = CurrentDb
Set tbl = db.CreateTableDef("ABCD_Temp")
tbl.Fields.Append tbl.CreateField("Location", dbText, 255)
tbl.Fields.Append tbl.CreateField("CreatedBy", dbText, 255)
tbl.Fields.Append tbl.CreateField("ResNum", dbText, 255)
tbl.Fields.Append tbl.CreateField("CNum", dbText, 255)
tbl.Fields.Append tbl.CreateField("VNum", dbText, 255)
tbl.Fields.Append tbl.CreateField("SNum", dbText, 255)
tbl.Fields.Append tbl.CreateField("ST", dbText, 255)
tbl.Fields.Append tbl.CreateField("AN", dbText, 255)
tbl.Fields.Append tbl.CreateField("Name", dbText, 255)
tbl.Fields.Append tbl.CreateField("Address", dbText, 255)
tbl.Fields.Append tbl.CreateField("City", dbText, 255)
tbl.Fields.Append tbl.CreateField("St", dbText, 255)
tbl.Fields.Append tbl.CreateField("Zip", dbText, 255)
tbl.Fields.Append tbl.CreateField("Phone", dbText, 255)
tbl.Fields.Append tbl.CreateField("RDA", dbCurrency)
tbl.Fields.Append tbl.CreateField("SDA", dbCurrency)
tbl.Fields.Append tbl.CreateField("TID", dbCurrency)
tbl.Fields.Append tbl.CreateField("Green", dbText, 255)
tbl.Fields.Append tbl.CreateField("CD", dbText, 255)
db.TableDefs.Append tbl
db.Close
Set fld = Nothing
Set tbl = Nothing
Set db = Nothing
MsgBox "Table created successfully.", vbInformation
End Sub
Then trying to import the file with vba....it was working until the requirement to split on the hyphen into two diff columns
Code:
Private Sub ImportSelectedFile(filePath)
Dim db As Database
Dim tbl As TableDef
Dim rs As Recordset
Dim row As Variant
Dim i As Integer
Dim j As Integer
Set db = CurrentDb
Set tbl = db.TableDefs("ABCD_Temp")
Set rs = tbl.OpenRecordset
Dim xlApp As Object
Dim xlWorkbook As Object
Dim xlWorksheet As Object
Set xlApp = CreateObject("Excel.Application")
Set xlWorkbook = xlApp.Workbooks.Open(filePath)
Set xlWorksheet = xlWorkbook.Sheets(1)
For i = 2 To xlWorksheet.UsedRange.Rows.Count
row = xlWorksheet.Rows(i).Value
rs.AddNew
For j = 1 To 17
If j = 4 And InStr(1, row(1, j), "-") > 0 Then
Dim parts() As String
parts = Split(row(1, j), "-")
rs.Fields(j - 1).Value = parts(0)
rs.Fields(j).Value = parts(1)
j = j + 1
ElseIf j < 4 Then
rs.Fields(j - 1).Value = row(1, j)
Else
rs.Fields(j).Value = CStr(row(1, j - 1))
End If
Next j
rs.Update
Next i
xlWorkbook.Close False
xlApp.Quit
Set xlWorksheet = Nothing
Set xlWorkbook = Nothing
Set xlApp = Nothing
rs.Close
db.Close
Set rs = Nothing
Set tbl = Nothing
Set db = Nothing
MsgBox "Data imported successfully.", vbInformation
End Sub
I have a button on a form, where the user selects the file