G
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.
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.