Avatar of leezac
leezac
 asked on

Importing Excel Files

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
Microsoft Access

Avatar of undefined
Last Comment
Jeffrey Coachman

8/22/2022 - Mon
Jeffrey Coachman

<Is there a way to modify this code to import more than one file?>
Import what, into what?
Access-->Excel
Excel-->Access
?

<I need to search for all of them>
Are you also asking how to "search" as well?

You can import multiple files from the Access side quite easily, as long as your files are consistently named, ex:
OLE*.xls

Dim strFolder As String
Dim strCurrentFile As String

strFolder = "c:\YourFolder\"
strCurrentFile = Dir(strFolder & "OLE*.xls")

Do While strCurrentFile <> ""
    DoCmd.TransferSpreadsheet acImport, , strCurrentFile, strFolder & strCurrentFile, True
    strCurrentFile = Dir
Loop


JeffCoachman
leezac

ASKER
Excel to Access.

Import Excel to Access and place in one table?
ASKER CERTIFIED SOLUTION
Jeffrey Coachman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
leezac

ASKER
Thank you!
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Jeffrey Coachman

;-)