PC Review


Reply
Thread Tools Rate Thread

TransferSpreadSheet help

 
 
Scooby912
Guest
Posts: n/a
 
      1st Aug 2003
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


 
Reply With Quote
 
 
 
 
Ken Snell
Guest
Posts: n/a
 
      2nd Aug 2003
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
<MS ACCESS MVP>

"Scooby912" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
>
>



 
Reply With Quote
 
 
 
 
Ken Snell
Guest
Posts: n/a
 
      5th Aug 2003
Only by VBA code...macros cannot do this.

Do you want to use VBA?

--
Ken Snell
<MS ACCESS MVP>

"Scooby912" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Thanks Ken,
>
> I was able to import the files in, but can't get the "flag" working.
> Instead of renaming the files, is there a way I can "move" the files to a
> different directory?
>
> Erwin.
>
>
> "Ken Snell" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> > <MS ACCESS MVP>
> >
> > "Scooby912" <(E-Mail Removed)> wrote in message
> > news:%(E-Mail Removed)...
> > > 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
> > >
> > >

> >
> >

>
>



 
Reply With Quote
 
Scooby912
Guest
Posts: n/a
 
      5th Aug 2003
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



"Ken Snell" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Only by VBA code...macros cannot do this.
>
> Do you want to use VBA?
>
> --
> Ken Snell
> <MS ACCESS MVP>
>
> "Scooby912" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > Thanks Ken,
> >
> > I was able to import the files in, but can't get the "flag" working.
> > Instead of renaming the files, is there a way I can "move" the files to

a
> > different directory?
> >
> > Erwin.
> >
> >
> > "Ken Snell" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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
> > > <MS ACCESS MVP>
> > >
> > > "Scooby912" <(E-Mail Removed)> wrote in message
> > > news:%(E-Mail Removed)...
> > > > 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
> > > >
> > > >
> > >
> > >

> >
> >

>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
DoCmd.TransferSpreadsheet help =?Utf-8?B?Y2xpZmY=?= Microsoft Access VBA Modules 4 24th Feb 2004 09:40 PM
DoCmd.TransferSpreadsheet help Tom & Roxanne Hanson Microsoft Access Queries 1 31st Jan 2004 05:08 AM
Help! Help! Help! Help! Help! Help! Help! Help! Help! Help! Help! Help! Help! -$- Windows XP Internet Explorer 2 21st Dec 2003 11:45 PM
Help w/ TransferSpreadsheet esi Microsoft Access External Data 0 19th Dec 2003 08:31 PM
TransferSpreadsheet/OutputTo Spreadsheet Help Joe R. Microsoft Access Macros 2 14th Aug 2003 02:28 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 10:03 PM.