Automatically Import when a file is modified

[Guest]

Here is my issue...

I have a file that gets kicked off our mainframe nightly. The file is
requested as an Excel file. Although when kicked off, the file appears to be
in Excel format and ends with a .xls extension, the file is really a teb
delimmetted file. Therefore, every morning I come in and save it as a "true"
..xls file and then import to my database via a form.

I think I can create a macro in Excel that will resave the file as an .xls
file automatically each night.

Here would be the ideal process:

- 3:10 a.m. every night, file is sent from mainframe to F:/ drive.
- macro runs that changes the file type to .xls
- Access DB imports file and then I can set the rest of the code to run in
the "import" event for this file.

Where I think it gets tricky, is there will be two modifications of the file
every night, and I only want to import after the second modification. I
suppose I could create the Excel Macro to automatically change the file to a
..tab extension. I guess then t 3:10 an filename.xls will have a new modified
date and then after the macro the filename.tab will have a new modified date
and on the change in modified date for filename.tab would trigger the import.
Is this sound reasoning?

 

[Guest]

If it is actually a tab delimited text file, have you tried using
TransferText instead of TransferSpreadsheet.
Go through the import process once manually and set up an import spec for it.
Then you can bypass the reformatting part.

 

[Guest]

Thank you very much for the reply. I did try that and didn't have much luck.

I have the job scheduled to run every night on our mainframe. The job
"allows" me to request my own file name. So, I thought that if I just named
the file Open.tab (as opposed to .xls) I could create a spec and use the
trasfertext command. The problem that I came across is that even though the
job that I scheduled appears to let me choose my file name and extension,
when I put in Open.tab for the output file name the file does not get
created. The file is only created if I instruct our mainframe to create a
file with a .xls extension.

I am not too worried about changing the file name to a true Excel file. I
have no idea how to code my DB to recognize when a file is modified to import
and then run the rest of the routine I have coded. I thought I saw in some
other posts (that are way over my head) that it was possible.

If I can get the file to import automatically, then ideally the end result
would be my final results being e-mailed to recipients.

I am completely self taught, so I apologize if I don't use correct
termnology or if this is an inappropriate question. I have tried looking at
online help and didn't see much, so please don't think I am not putting my
own effort into this, I really have. I just don't have the training or
experience that I desperately need and want. (

Thanks,
Roger

 

[Douglas J. Steele]

Can you have them create a file with an .xls extension, and then rename it
in VBA (using the Name statement)?

 

[Guest]

Thank you very much for the reply. I am unfamiliar with the Name statement,
so I will have to look in online help to evevn have a frame of reference and
if it would help.

As I indicated, the file has a .xls extension, however really is a .tab
file. I also tried a Transferspreadsheet command for the .xls file, but
receive an error message stating "file not in expected format". So, it seems
I can't "trick" access. Therefore, at this point I am resaving manually.

But, I think I can get around the renaming of the file in an Excel macro.
What I am most interested in is how can I go about getting my Access DB to
look at a certain filename and file path (both the same every night) and
"realize" the modified date (or some other indicator - I can even use an
"import every night at 4:00 am" type thing) and import the file. After that,
I can get the rest of the routine to run, I think.

Thank you,
Roger

 

[Douglas J. Steele]

Name "C:\Folder\File.xls" AS "C:\Folder\File.tab"

will change the name of the file from File.xls to File.tab

You'd then use TransferText, not TransferSpreadsheet.

 

[Roger Converse]

Excellent. I can certainly handle that. Thank you.

Is there a way to make my db recognize when this file has been renamed so
that I can automate the import and the rest of the routine?

Thank you,
Roger

 

[Douglas J. Steele]

Not sure what you mean by "recognize when this file has been renamed". You'd
put the rename statement in the VBA code, and import right after issuing it.

 

[Roger Converse]

Every evevning, a file named Open.xls will be saved out to a filepath
overwriting the previous copy. Is there a way to have access recognize that
file has been resaved or the modified date has changed so that the renaming
and importing process will automatically start in may access db (as opposed
to importing through a form's import button)?

Thanks,
Roger

 

[Douglas J. Steele]

The FileDateTime function will give you the last modified date of the file:
FileDateTime("C:\Folder\File.xls"). Store the previous value in a table, and
check whether or not it's changed.

If that's not sufficient, there are APIs that will watch a folder for you.
Randy Birch has an example at
http://vbnet.mvps.org/index.html?code/fileapi/watchedfolder.htm

(Obligatory warning: Randy's site is aimed at VB programmers, not Access
programmers. Because there are some significant differences between the
controls available for forms in VB and in Access, some of his examples don't
port directly to Access. For example, depending on what version of Access
you're using, the use of the .AddItem to populate the listbox List1 may not
work. The underlying code to interact with the APIs, though, should work
fine.)