File handling / datetimestamping objects in VBA.

[russellfowden]

I'm building a VBA app that processes and imports data from comma delimited files with a time/date stamp in the filename. These are automatically dropped into a holding directory at particular intervals, hence the potential for several to process sequentially. I'd like to maintain three folders - "received", "current" and "archive." New files would be dropped into "received" and have a filename format something like "file_ddmmyyyy-hh:mm:ss.csv." On a click event I'd like the following to happen:

1 - Several files may have been dropped into the "received" directory. I want to parse the filename, identify the one with the oldest timestamp (and probably pull the name into a variable) then put a copy of that file into the "archive" directory with the name unchanged.

2 - Copy the same file from "received" into "current" and rename it along the way to something like "nextin.csv".

3 - Run the parsing and insertion of the data through an API process (I've already finished this bit.)

4 - After successful processing delete the original csv from "received" and also the copy from "current."

I can't seem to find file handling objects that give me the tools I need to do stages 1,2 and 4. Does anyone happen to have any sample code or general approaches I could start with?

Many thanks,

Russ

 

[PHV]

Have a look here:

http://msdn.microsoft.com/library/en-us/script56/html/af4423b2-4ee8-41d6-a704-49926cd4d2e8.asp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[russellfowden]

Thank you very much, that definitely goes some of the way -the filesystemobject will help me access and read the file once I've moved it to a working folder and hardwired the name. I guess to be more elegant I could bring in the whole path/name into a variable and work with that.

Can't for the life of me work out how I'm going to look at the dropoff folder, pick the oldest file from the timestamp in the name and then process that first though. If anyone knows of any sample code achieving something similar I'd be delighted to see it!

Cheers,

Russ

 

[PHV]

What have you tried so far ?
Where in your code are you stuck ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[russellfowden]

Very little, I'm not a professional developer and have never tried this file handling stuff in vb code before (other than simple things like 'open "c:\blah\yadda.csv" for output as #1') - I've been finishing off the rest of it, in this post I was trying to just get an approach to start with.

If I break it down into chunks I guess the first part would be to look at the contents of a directory "x:\jpmimport\received" which would contain for example the following files that have been automatically delivered by an email process outside my control:

JPM_06022007-10:43:06.csv
JPM_06022007-11:21:48.csv
JPM_06022007-13:44:23.csv

What I'd be trying to achieve would be to read in the properties (and perhaps file date metadata if that's easier to work with) of those multiple files in the hard coded location, determine that "JPM_06022007-10:43:06.csv" was the oldest file (this could be done from the file modified attribute rather than from parsing the name if this was easier) and to then set this file name as a variable. I could then use the filesystem object to copy, rename and delete it from/in other folders. However I think that's a relatively complex bit of code and I was just hoping that somebody would have done this before and be able to send me an example!

I've had a good old google and nobody seems to have posted an approach to this before, which is weird because I'd have thought there are many mapping applications out there that probably do this sort of thing.

Russ

 

[fumei]

However I think that's a relatively complex bit of code and I was just hoping that somebody would have done this before and be able to send me an example!

People have done this before. However, this is not a Help Desk.

Yes, breaking things down into to chunks is a VERY good idea.

I will reiterate what PHV posted.

What have you tried so far ?
Where in your code are you stuck ?

Post what you HAVE tried.


Gerry
My paintings and sculpture

 

[russellfowden]

I gave up and did this by generating DOS batch syntax and then executing it with a shell statement in the end. I'm sure there's more elegant ways of doing it with file handling objects but didn't get chance to work it out. Here's my code:

'Make a copy of import file that user has dropped into JPM_SOURCE then rename original
'by generating then running a one off batch file.

batpath = userPath & "JPM_SOURCE\"

Open batpath & "CopyRename.bat" For Output As #1

Print #1, "copy " & "c:\jpmimport\" & Worksheets("Mappings").Range("TR_USER") & _
"\JPM_SOURCE\" & "JPM*.csv " & "c:\jpmimport\" & Worksheets("Mappings").Range("IN_USER") & _
"\JPM_SOURCE\archive\" & Chr(13) & Chr(10) & _
"ren " & "c:\jpmimport\" & Worksheets("Mappings").Range("TR_USER") & _
"\JPM_SOURCE\" & "JPM*.csv nextin.csv"

Close #1

batfile = batpath & "CopyRename.bat"

Shell batfile

'Give it time to rename the input csv

Application.Wait Now + TimeValue("00:00:02")

Set fromWkBk = Workbooks.Open("c:\jpmimport\" & Worksheets("Mappings").Range("TR_USER") & _
"\JPM_Source\nextin.csv")

 

[PHV]

And what about this (without FSO) ?
srcDir = "c:\jpmimport\" & Worksheets("Mappings").Range("TR_USER") & "\JPM_SOURCE\"
dstDir = "c:\jpmimport\" & Worksheets("Mappings").Range("IN_USER") & "\JPM_SOURCE\archive\"
srcFile = Dir(srcDir & "JPM*.csv")
If srcFile = "" Then Exit Sub
FileCopy srcDir & srcFile, dstDir & srcFile
Name srcDir & srcFile As srcDir & "nextin.csv"
DoEvents
Set fromWkBk = Workbooks.Open(srcDir & "nextin.csv")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886