TransferSpreadSheet help

[Scooby912]

I am currently using the TransferSpreadsheet command in a macro. But
everytime I have a new file, I would go in the macro and change the
date/time portion of the filename before I can run the command.

All the Excel files have the same filename format
(filenamemmddyy_hhmmss.xls) with the filename the same and just date/time
changing.

Is there a way to have the macro flag the files it already read and then
proceed to the unread files and import them?

Thanks,
Erwin

 

[Ken Snell]

The macro cannot do this on its own, but there are various ways you can
"overcome" this. Here's probably the easiest one.

In the File Name box for the macro action, type an expression similar to
this:

="FILENAME" & InputBox("Enter the date that's in the file name (use
mm/dd/yy format):") & "_" & InputBox("Enter the time that's in the filename
(use hhnnss format):") & ".xls"

This will cause the macro to prompt you for the date and the time and then
use them in concatenating the filename.

If you want to "flag" files somehow, you'd need to use VBA code (not a
macro) and do something with each file after it's been "read" by the code.
You could change the name of the file so that it no longer has the "format"
(such as adding "READ" onto the front of the filename), or you could move
the file into a different folder so that it's no longer there for the code
to "see" when it runs. Post back if you need more info about these options.

 

[Ken Snell]

Only by VBA code...macros cannot do this.

Do you want to use VBA?

 

[Scooby912]

I don't really know how to program, but I have some VBA codes that are
running and I am able to understand and manipulate them. If you can point me
in the right direction, I'm pretty sure that I can work something out.

I have this code that I am working on, but I'm not sure it's the
right/proper way of doing it. It works, but as you can see the drive letters
are the "C" drive. I tried pointing it to the network, using a UNC path and
it wouldn't work.

ChDrive "C"
ChDir "C:\Temp\Old"
Name strPath & strFileName As strPath & strFileName & ".old"

Erwin