Importing from Excel via VBA Code

[LeftyLeo via AccessMonster.com]

I am trying to import a spreadsheet into access via VBA. There is a field in
the spreadsheet that will not import any other way. It gives import errors
on the invoice number. So, I have this code below but it is in a perpetual
loop will not stop until you end the program. Can someone tell me what I am
missing or doing wrong, please. Should it be Do until end of file. Not sure
how to code it. The blLoop is not ever false, not sure why?

Private Sub cmdImport_Click()
Dim objApp As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objSheet As Excel.Worksheet
Dim strYear As String
Dim strPeriod As String
Dim strDivision As String
Dim strStore As String
Dim strJournal As String
Dim strJournalDesc As String
Dim dblJournalAmount As Double
Dim strAccountNumber As String
Dim strOwner As String
Dim strVendorName As String
Dim strInvoiceNumber As String
Dim lngRow As Long
Dim blLoop As Boolean
Dim rec As ADODB.Recordset


DoCmd.Hourglass True


'Open Excel workbook

If IsNull(Me.txt029803File.Value) Then
MsgBox "MISSING FILE NAME -- Please Click in the Excel File Path text
box to start File Dialog Box.", vbInformation + vbOKOnly, "Need File Name"
Exit Sub
End If

Set objApp = New Excel.Application
Set objWorkbook = objApp.Workbooks.Open(Me.txt029803File.Value)
Set objSheet = objWorkbook.Sheets("P1")

ClearTable ("tblImport")

Set rec = New ADODB.Recordset
rec.Open "tblImport", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

lngRow = 1
blLoop = True

Do Until blLoop = False
lngRow = lngRow + 1

strYear = objSheet.range("A" & lngRow).Value
strPeriod = objSheet.range("B" & lngRow).Value
strDivision = objSheet.range("C" & lngRow).Value
strStore = objSheet.range("D" & lngRow).Value
strJournal = objSheet.range("E" & lngRow).Value
strJournalDesc = objSheet.range("F" & lngRow).Value
strAccountNumber = objSheet.range("G" & lngRow).Value
strOwner = objSheet.range("H" & lngRow).Value
strVendorName = objSheet.range("I" & lngRow).Value
strInvoiceNumber = objSheet.range("J" & lngRow).Value
dblJournalAmount = objSheet.range("K" & lngRow).Value

rec.AddNew
rec("Year") = strYear
rec("Period") = strPeriod
rec("Division Number") = strDivision
rec("Journal Amount") = dblJournalAmount * -1
rec("Store") = strStore
rec("Journal") = strJournal
rec("Journal Description") = strJournalDesc
rec("Account Number") = strAccountNumber
rec("Owner") = strOwner
rec("Vendor Name") = strVendorName
rec("Invoice Number") = Left(strInvoiceNumber, InStr(strInvoiceNumber, "_"))

Loop



objWorkbook.Close SaveChanges:=False
objApp.Quit
DoCmd.Hourglass False

'On Error GoTo ErrHandler
'DoCmd.DeleteObject acTable, "tblImport"
'DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "tblImport", Me.
txt029803File, True

MsgBox "File imported. Go to Step 2", vbInformation, "Import Complete"

ErrHandler:
If Err.Number = "7874" Or Err.Number <= 20 Then
Resume Next
Else
MsgBox Err.Number & "Please select a file to import.", vbInformation, "No
File"
End If
End Sub

 

[Douglas J. Steele]

You've got

Do Until blLoop = False

but you don't set blLoop to False anywhere in your code. Somewhere in that
loop, you have to determine when you're out of data (when objSheet.range("A"
& lngRow).Value doesn't contain a value?), and set blLoop to False.

 

[LeftyLeo via AccessMonster.com]

You've got

Do Until blLoop = False

but you don't set blLoop to False anywhere in your code. Somewhere in that
loop, you have to determine when you're out of data (when objSheet.range("A"
& lngRow).Value doesn't contain a value?), and set blLoop to False.

I am trying to import a spreadsheet into access via VBA. There is a field
in

[quoted text clipped - 101 lines]

End If
End Sub

Thank you I will try to code the objSheet.range value to IsNull then blLoop =
false and see if that does it. If not I will let you know. Thanks again
Doug you've help me countless times and I am very appreciative.

 

[Dale Fye]

Lefty,

I hate that Microsoft decided to let Access decide what the field types are
when importing an Excel spreadsheet. They should have provided the same
Import Specification capability with spreadsheets as they did with text
files. I'll get off my soapbox now.

Have you tried saving the Excel worksheet as a tab delimited text file, then
importing it. The advantage of this is that you can define an Access Import
Specifications to ensure the data is imported using the appropriate data
type. You could even use automation to open the Excel file, select the
appropriate range in the worksheet you want to export, then save it at text.

ActiveWorkbook.SaveAs Filename:= "C:\FILES\TEST.csv", FileFormat:=xlCSV,
CreateBackup:=False, Local:=True

Then use the docmd.TransferText method with a defined import specification.

HTH
Dale