Link to home
Start Free TrialLog in
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
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

<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
Avatar of leezac
leezac

ASKER

Excel to Access.

Import Excel to Access and place in one table?
ASKER CERTIFIED SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of leezac

ASKER

Thank you!