Array question...newbie programmer needs help!!!

[Steve P]

Hello all of you VBA oracles out there...

I am creating a script that appends excel files to an exisiting table. I
have the basic import code working fine, I just need to fine tune it to track
which input file any particular record was added with.

I am attempting to create a code module that checks the contents of a
specific folder, locates the most recently created file in the folder based
on file name (which will have date/time stamped file names ie.
072407_0830_blahblah.xls), and saves the date/time stamp in a variable. The
value in the variable at the end of the code would have a value of
"072407_0830" in this case. I'm thinking that this would best be
accomplished by using an array...what do you all think?

This variable will be used later on in the script in a field I have yet to
create to stamp which file the record was imported from. I will also have
the script do a check of the "importstamp" field to see if all of the most
recent files have been imported...in other words, I'd like the script to only
append Excel files that have not previously been appended.

 

[pietlinden]

Hello all of you VBA oracles out there...

I am creating a script that appends excel files to an exisiting table. I
have the basic import code working fine, I just need to fine tune it to track
which input file any particular record was added with.

I am attempting to create a code module that checks the contents of a
specific folder, locates the most recently created file in the folder based
on file name (which will have date/time stamped file names ie.
072407_0830_blahblah.xls), and saves the date/time stamp in a variable. The
value in the variable at the end of the code would have a value of
"072407_0830" in this case. I'm thinking that this would best be
accomplished by using an array...what do you all think?

I disagree. Use Doug's function. then just pass that filename to
your import process (TransferSpreadsheet)... then you should be done.

This variable will be used later on in the script in a field I have yet to
create to stamp which file the record was imported from. I will also have
the script do a check of the "importstamp" field to see if all of the most
recent files have been imported...in other words, I'd like the script to only
append Excel files that have not previously been appended.

If all the existing records in the table have an "import date" value
in that field, update only the records with null values...

SHAMELESSLY PILFERED CODE FROM Doug Steele...
You can loop through a folder using the Dir function. You can get the
Last
Modified date of a file using the FileDateTime function.

Dim dtmCurrFile As Date
Dim dtmMostRecent As Date
Dim strFolder As String
Dim strFile As String
Dim strMostRecent As String

dtmMostRecent = #1/1/1970#
strFolder = "C:\MyFolder\" ' Note that the final slash is
critical!
strFile = Dir$(strFolder & "*.txt")
Do While Len(strFile) > 0
' strFolder & strFile is a file in the folder
dtmCurrFile = FileDateTime(strFolder & strFile)
If dtmCurrFile > dtmMostRecent Then
dtmMostRecent = dtmCurrFile
strMostRecent = strFolder & strFile
End If
strFile = Dir$()
Loop

MsgBox "The most recent file in " & strFolder & _
" is " & strMostRecent & vbCrLf & _
"It has a date of " & dtmMostRecent