Importing Excel Files

leezac
leezac used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<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

Author

Commented:
Excel to Access.

Import Excel to Access and place in one table?
MIS Liason
Most Valuable Expert 2012
Commented:
Dim strFolder As String
Dim strCurrentFile As String

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

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

Author

Commented:
Thank you!
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
;-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial