troubleshooting Question

Importing Excel Files

Avatar of leezac
leezac asked on
Microsoft Access
5 Comments1 Solution318 ViewsLast Modified:
Is there a way to modify this code to import more than one file?
Say I have OLE.xls and OLE3.xls and OLE4.xls and I need to search for all of them and import but there could be more or less of the files saved to import. I am just asking.  If there is a way I need to add to my code.  Also to import into same table....  Thank you!

Public Function importSecDist()
''Imports rows from spreadsheets finding first header row

Dim cnn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ws As Excel.Application
Dim i As Long
Dim StrFndNmbr As String
StrFndNmbr = ("Account Number")
   
On Error GoTo err_handler

DoCmd.SetWarnings False

DoCmd.RunSQL "DELETE * from tblSecDist"
DoEvents
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
Set ws = CreateObject("Excel.Application")
With ws
.Workbooks.Open "P:\ole.xls"
.Visible = True
End With

Dim lngCount As Long
rst.Open "tblSecDist", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
ws.Sheets("Security Distribution").Select
''--ws.Range("a9").Select
ws.Range("A:A").Find(What:=StrFndNmbr, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Select


Do Until ws.ActiveCell.Value = ""
With rst
.AddNew
.Fields("Account Number").Value = ws.ActiveCell.Offset(1, 0).Value
.Fields("Security Number (Full)").Value = ws.ActiveCell.Offset(1, 1).Value

.Update
lngCount = lngCount + 1
End With
ws.ActiveCell.Offset(1, 0).Select
Loop
ws.Range("A1").Select
rst.Close
ws.Quit
DoCmd.SetWarnings True
Exit Function
err_handler:
DoCmd.SetWarnings True
MsgBox Err.Description

rst.Close
ws.Quit
End Function
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros