Hi Sydious,
Using VBA you are still better off having a work folder and a processed folder.
For this paradigm you would need \work & \work\processed
Using VBA you would create a filesystemobject, go pick up the names of the files in the work folder. If they have a common part and a date part, you can figure out which one you are dealing with by that. If I knew how the names were constructed, then I could be more specific.Drag the new spreadsheets into the work folder.
These 3 workbooks with 2 sheets (that matter) each. Are they all the same internally? In other words, are you importing six sheets of information that have the same headers?
If so, then you don't have to know which one is which. Just pick up the names and go for it with the docmd.transferspreadsheet macro, then move the files over to the processed folder.
NOTE: This solution requires that you have (or add) a reference to the Microsoft Scriptlet Library - Alt-F11, References, select the Microsoft Scriptlet Library and click okay.
NOTE: You will need to replace the MYTABLENAMEGOESHERE with your table name in the docmd.
NOTE: I haven't tested this code - could be a typo or something so caveat emptor - no warranty expressed or implied etc etc. but with minor adjustment it should work.
NOTE: You can call the function with the sub as shown - or you could assign the function to a Macro.
NOTE: Obviously as with any automated chore you have to be a little careful - don't import the same set of files twice, etc. etc.
I have trapped over 3 file or less than 3 files, set up a msg and exited. You can adjust this as suits your purposes. For instance it might be okay to import 1 or 2 files. However as the inline comment states - you must test and make sure at least 1 file has been found or the subsequent code will break all over with who knows what possible side effects. The earth might melt with a fervent heat! <grin> I have dimensioned the fArray (which stores the file names we read in from the folder) at 3 so if you want to increase the file count you will need to change that index, make it whatever is appropriate, and adjust the inline test for exceeding the max file count.
If the workbooks aren't the same and you need to identify them and maybe send them to different tables, then you need to take out the loops and step through the filenames one at a time, maybe use a Select Case to handle the sorting and then run the appropriate docmd.transferspreadsheet maco.
Sub Import()
msgbox "Importing: " & vbCrLf & ProcessFileNames(), vbInfo, "Spreadsheet Import Utility"
end Sub
Public Function ProcessFileNames() As String
Const myPath = "C:\work"
Dim fs As Object 'Script Object
Dim myFolder As Object 'Folder Object
Dim f As Variant 'enumerator for files collection
Dim myFile As String 'scratch string to build file name
Dim i As Integer 'loop counter
Dim x As Integer 'loop counter
dim z As Integer 'stores the count of files
Dim fArray(3) As String 'stores the file names
'should never be more than 3 files
'if there is, we want it to break on this
i = 1
Set fs = CreateObject("Scripting.FileSystemObject")
Set myFolder = fs.GetFolder(myPath)
For Each f In myFolder.Files
'if we exceed three files then we will break our array
'so we send a msg and exit before importing or processing
If i > 3 Then
MsgBox "Too many files in the " & myPath & " folder! Cannot continue...", vbCritical, "Too Many Files"
Exit Function
End If
fArray(i) = f.Name
i = i + 1
Next f
z = i - 1
'#######################
'If you want to accept 1 or 2 files
'change the z<3 to z<1 or z<2 but
'you *must* at a minimum test z< 1
' making sure you have at least one file
'or bad things will happen
'#######################
If z < 3 Then
MsgBox "Not enough files in the " & myPath & " folder! Cannot continue...", vbCritical, "Not Enough Files"
Exit Function
End if
For i = 1 To z
For x = 1 To 2
myFile = myPath & "\" & fArray(i) & "!Sheets(" & x & ")"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel4, "MYTABLENAMEGOESHERE", myFile, True
Next x
Next i
For i = 1 To z
ProcessFilesNames = ProcessFileNames & fArray(i) & vbCrLf
fs.MoveFile myPath & "\" & fArray(i), myPath & "\processed\ & fArray(i)"
Next i
Set myFolder = Nothing
Set fs = Nothing
End Function
Hope this helps,
Gordon
Sydious said:
OK. What if I were to use VBA? I'm not VBA ignorent, actully I have written
some apps using VB6 in the past, but I don't have a ton of knowledge to draw
from. I am looking for an easy way to do this.
I figure the file name just needs to be built into a string and use that
string to call the files to import. Not sure how I would reference the form's
txtFileDate to add it to the string.
