TransferSpreadSheet help

Discussion in 'Microsoft Access Macros' started by Scooby912, Aug 1, 2003.

  1. Scooby912

    Scooby912 Guest

    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
     
    Scooby912, Aug 1, 2003
    #1
    1. Advertisements

  2. Scooby912

    Ken Snell Guest

    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" <> wrote in message
    news:%...
    > 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, Aug 2, 2003
    #2
    1. Advertisements

  3. Scooby912

    Ken Snell Guest

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

    Do you want to use VBA?

    --
    Ken Snell
    <MS ACCESS MVP>

    "Scooby912" <> wrote in message
    news:%...
    > 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" <> wrote in message
    > news:...
    > > 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" <> wrote in message
    > > news:%...
    > > > 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, Aug 5, 2003
    #3
  4. Scooby912

    Scooby912 Guest

    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" <> wrote in message
    news:...
    > Only by VBA code...macros cannot do this.
    >
    > Do you want to use VBA?
    >
    > --
    > Ken Snell
    > <MS ACCESS MVP>
    >
    > "Scooby912" <> wrote in message
    > news:%...
    > > 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" <> wrote in message
    > > news:...
    > > > 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" <> wrote in message
    > > > news:%...
    > > > > 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
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >
     
    Scooby912, Aug 5, 2003
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Greg

    TransferSpreadsheet

    Greg, Jul 8, 2003, in forum: Microsoft Access Macros
    Replies:
    1
    Views:
    1,079
    dennis
    Jul 10, 2003
  2. Joe R.

    TransferSpreadsheet/OutputTo Spreadsheet Help

    Joe R., Aug 13, 2003, in forum: Microsoft Access Macros
    Replies:
    2
    Views:
    1,105
    Joe R.
    Aug 14, 2003
  3. Clifford S. Morton

    TransferSpreadsheet

    Clifford S. Morton, Sep 8, 2003, in forum: Microsoft Access Macros
    Replies:
    0
    Views:
    540
    Clifford S. Morton
    Sep 8, 2003
  4. Beginner

    Transferspreadsheet - datatype error

    Beginner, Sep 17, 2003, in forum: Microsoft Access Macros
    Replies:
    0
    Views:
    555
    Beginner
    Sep 17, 2003
  5. Replies:
    2
    Views:
    2,911
    EagleOne@microsoftdiscussiongroups
    Jul 31, 2008
Loading...

Share This Page