|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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.
|
|
Answered in another newsgroup to which you posted the same question.
If you feel you need to post to more than one group (HINT: it's seldom
necessary), please have the courtesy to cross-post (send the one message to
all groups at once), rather than multi-post (send individual messages to
each group). In this way, all responses to your post will be available
together, regardless of what group the responder was in, and the rest of us
won't have to read your post multiple times. (It also uses fewer server
resources)
I see you're using Outlook Express. Click the "Newsgroups:" label to the
left of the box containing the name of the current newsgroup. That will open
a dialog that will let you add additional newsgroups to your post.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
<doctorjones_md[ at ]yahoo.com> wrote in message
news:uAYj2vdpGHA.4116[ at ]TK2MSFTNGP03.phx.gbl...
[Quoted Text] >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.
>
|
|
|