Importing Multiple Excell Files

[Guest]

I am wanting to import 100+ excell files that are stored in a central folder.
Does anyone have a simple VBA code that will allow me to import these all at
once rather then importing each excell file seperately? The excell files sent
in to monthly folders and I am wondering if there is a way to allow the user
to simply select the monthly folder which will run a macro to upload the
excell files contained within that folder?

 

[Guest]

A Macro is not going to do this for you. You will need some VBA code.

First, here is a link that has code that will allow the user to select a
folder.

http://www.mvps.org/access/api/api0002.htm

Once the user has selected the folder, you can use the Dir function to
return the names of the Excel files in the folder. The using a loop, you can
use the TransferSpreadsheet to import.

It would be something like this:

Dim strDirectory As String
Dim strFileName As String

strDirectory = BrowseFolder("Select A Folder")

strFileName = Dir(strDirectory, vbDirectoyr)

Do While strFileName <> vbNullString
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"SomeTable", _
strDirectory & "\" & strFilename, True
Loop

Now, the only other problem is how to dermine what table to import into.
You will need to add some logic to change the table name for each import.

 

[Ken Snell \(MVP\)]

A Macro is not going to do this for you. You will need some VBA code.

Hmmmmm, actually, it is possible to use a macro for importing numerous files
from a single folder < s >:

http://groups.google.com/group/micr...c.access.macros&rnum=4&hl=en#805e434beabce198

http://groups.google.com/group/micr...b2b1a?lnk=st&q=&rnum=1&hl=en#aa879d1dff2b2b1a


http://groups.google.com/group/micr...94f1e?lnk=st&q=&rnum=2&hl=en#2dbf1b377e294f1e


One could modify the above approach by adding the use of the folder
navigation process, which would involve using RunCode action to call a VBA
function that would do the folder navigation as noted:
http://www.mvps.org/access/api/api0002.htm

 

[Guest]

Well, Ken, okay. I will admit there is a lot about Macros I don't know
because I do not use them.
But, your solution also involves a RunCode which means we are right back to
VBA.
Thanks for pointing out my error.

 

[Guest]

I am sorry but I am a bit new to writing VBA codes.... So is this along the
lines of what the code should like?

Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias _
"SHGetPathFromIDListA" (ByVal pidl As Long, _
ByVal pszPath As String) As Long

Private Declare Function SHBrowseForFolder Lib "shell32.dll" Alias _
"SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) _
As Long

Private Const BIF_RETURNONLYFSDIRS = &H1
Public Function BrowseFolder(szDialogTitle As String) As String
Dim X As Long, bi As BROWSEINFO, dwIList As Long
Dim szPath As String, wPos As Integer

With bi
.hOwner = hWndAccessApp
.lpszTitle = szDialogTitle
.ulFlags = BIF_RETURNONLYFSDIRS
End With

dwIList = SHBrowseForFolder(bi)
szPath = Space$(512)
X = SHGetPathFromIDList(ByVal dwIList, ByVal szPath)

If X Then
wPos = InStr(szPath, Chr(0))
BrowseFolder = Left$(szPath, wPos - 1)
Else
BrowseFolder = vbNullString
End If

Dim strDirectory As String
Dim strFileName As String

strDirectory = BrowseFolder("Select A Folder")

strFileName = Dir(strDirectory, vbDirectoyr)

Do While strFileName <> vbNullString
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"SomeTable", _
strDirectory & "\" & strFilename, True
Loop

End Function

 

[Guest]

Not Quite, sorry I wasn't more clear.

The part you downloaded from the MVP site should go in a standard module by
itself. Then the part I wrote you can put in the Click event of a command
button on a form. That would be this part:
(I added the function name so it makes more sense. You would not need to
put in the function name or the End Function line.

In design view of your form, select or create the command button, the open
the properties dialog, select the events tab, and select the cllick event.
Chose Code Builder and paste the code in the event sub.

Private Function cmdImport_Click()
Dim strDirectory As String
Dim strFileName As String

strDirectory = BrowseFolder("Select A Folder")

strFileName = Dir(strDirectory, vbDirectoyr)

Do While strFileName <> vbNullString
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"SomeTable", _
strDirectory & "\" & strFilename, True
Loop

End Function

 

[Ken Snell \(MVP\)]

Well, Ken, okay. I will admit there is a lot about Macros I don't know
because I do not use them.
But, your solution also involves a RunCode which means we are right back
to
VBA.
Thanks for pointing out my error.

No error on your part < g > ... in fact, just before posting that long-ago
original "solution" for a macro, I was ready to type that it couldn't be
done, and then all of sudden a flash of inspiration, and a half-hour
later.....

And yep, can't avoid VBA for the API call to the OpenFile dialog box.

 

[Guest]

So I have created a new Module in which the part downlaoded from the MVP site
was inserted. Then I created the Command Button and used code builder. My
Code builder text looks like this:

Private Sub Command1_Click()
Dim strDirectory As String
Dim strFileName As String

strDirectory = BrowseFolder("Select A Folder")

strFileName = Dir(strDirectory, vbDirectoyr)

Do While strFileName <> vbNullString
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"SomeTable", _
strDirectory & "\" & strFileName, True
Loop

End Sub

How do I click activate the code? It does not allow me to click the button.
Also how do I tie the two codes together so that when I click the command
button the directory table for choosing the folder comes up?