Importing EXCEL data into ACCESS

[Guest]

I have an Aggregate Transfer Spreadsheet Macro that runs a series of Delete
Queries and Individual Transfer Spreadsheet Macros to import my EXCEL data
into ACCESS. I've been tasked with additional criteria that I'm having
problems with:

At this point, I'm not even sure if a Macro can handle all these additional
criteria -- Is there a way (In ACCESS) to accomplish the following:

1. Have a form (or similar interface) where a user could specify which
corresponding EXCEL Spreadsheets they would like the Macro to Transfer?

For example: Say, my directory path is C:\\Temp\ and I have 8
individual workbooks -- (in my Macros, I've specified the range:
PC_Budget_Upload_SR-X!A4:R257) -- I just need a way for the user to choose
"Import All Workbooks" or to "Specify Individual Workbooks" to import -- I'm
guess that a form might be the way to go on this, but how would you work the
logic behind the drop-down selection? In other words, how does the user
selection on the form get updated in the Macro (or VBA code)?

Here's the (modified) code I have that allows me to "enter" the File Name &
Worksheet Name for a single file (Note: This code was originally written to
Loop through a directory and import all files having *.xls -- I need for the
code to allow me to choose individual files from that directory:

Sub Import_From_Excel()
'Macro Loops through the specified directory (strPath)
'and imports ALL Excel files to specified table in the Access
'Database.

Const strPath As String = "E:\AL1403 05-06\" 'Directory Path
Dim strSheetName As String 'Worksheet Name
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number

'Loop through the folder & build file list
strFile = Dir(strPath & "*.xls")
strFileName = InputBox("Enter the name of the file.")
strSheetName = InputBox("Enter the worksheet name.")
'While strFile <> ""
'add files to the list
'intFile = intFile + 1
' ReDim Preserve strFileList(1 To intFile)
' strFileList(intFile) = strFile
'strFile = Dir()
'Wend
'see if any files were found
'If intFile = 0 Then
'MsgBox "No files found"
'Exit Sub
'End If
'cycle through the list of files & import to Access
'creating a new table called MyTable
'For intFile = 1 To UBound(strFileList)
DoCmd.TransferSpreadsheet acImport, , _
"Wednesday_Check", strPath & strFile, True, strSheetName &
"!A4:T257"
'Check out the TransferSpreadsheet options in the Access
'Visual Basic Help file for a full description & list of
'optional settings
'Next
'MsgBox UBound(strFileList) & " Files were Imported"
End Sub
=========================================================
Here is some code which allows you to select Multiple Files to open -- could
a variation of this code give me what I'm looking for? And IF so, how would
I incorporate it into my original code?

Sub OpenMultipleFiles()
Dim Filter As String, Title As String, msg As String
Dim i As Integer, FilterIndex As Integer
Dim Filename As Variant
' File filters
Filter = "Excel Files (*.xls),*.xls," & _
"Text Files (*.txt),*.txt," & _
"All Files (*.*),*.*"
' Default filter to *.*
FilterIndex = 3
' Set Dialog Caption
Title = "Select File(s) to Open"
' Select Start Drive & Path
ChDrive ("E")
ChDir ("E:\AL1403 05-06")
With Application
' Set File Name Array to selected Files (allow multiple)
Filename = .GetOpenFilename(Filter, FilterIndex, Title, , True)
' Reset Start Drive/Path
ChDrive (Left(.DefaultFilePath, 1))
ChDir (.DefaultFilePath)
End With
' Exit on Cancel
If Not IsArray(Filename) Then
MsgBox "No file was selected."
Exit Sub
End If
' Open Files
For i = LBound(Filename) To UBound(Filename)
msg = msg & Filename(i) & vbCrLf ' This can be removed
Workbooks.Open Filename(i)
Next i
MsgBox msg, vbInformation, "Files Opened" ' This can be removed
End Sub


2. The other requirement is that we need the File Path of the imported
EXCEL file (as well as the Date/Time of the import into ACCESS) to be
populated in the destination table in ACCESS (Note: these (2) data fields
do not currently exist in the Spreadsheets being imported -- they need to be
created once they're imported into ACCESS.

Any thoughts on how I can approach this will be greatly appreciated --
thanks in advance.

 

[Ken Snell \(MVP\)]

You can use an expression in the macro's Range argument for
TransferSpreadsheet to return the desired worksheet you want to import. (by
the way, it looks like you're using VBA code, not a macro -- they're very
different things in ACCESS)

VBA code can be written to read the value of a combo box from a form, and
then to use that value to decide what to do for the import process --
whether to import a single worksheet, whether to import all worksheets, etc.
The trick may be that ACCESS does not know directly what the names of the
worksheets are in the file until it opens them. (I seem to recall a post by
someone who had posted how to do a query or something like that on the
workbook file to get a list of the worksheet names without having to open
EXCEL file first -- can't find that post right now, but a Google search
might turn it up...it was within the past 2 years, I believe.)

As for the filename's path, that too can be handled by VBA code, as you must
provide that to the TransferSpreadsheet action anyway. So you can store that
in a variable and the write that value into a table's field later on.

From the looks of the code that you have posted so far, you appear to have
good VBA experience, so let me stop here and let you ask the next questions
for what you want to do.
--

Ken Snell
<MS ACCESS MVP>