Excel Data Transfer Issue (strange situation)

[JHB]

Hi:

I have an Excel sptreadsheet which consolidates data from a number of
spreadsheets and edits into a form that I can import into Access and
use for other processes -- BUT I HAVE A WEIRD PROBLEM.

To beigin with, you should be aware that the on opening, the
spreadsheet does its thing and then saves itself and closes Excel (and
itself). I am making this happen by executing the following commands
within a macro (run after emptying the target table):

RUNAPP: C:\Program Files(x86)\Microsoft
Office\Office\EXCEL.EXE "C:\TASC\apps\TASC-DriverDataCollect.xls"

TRANSFERSPREADSHEET: Transfer Type: Import
MicrosoftExcel 8-9
(Using Excel from Office 2000)
TABLENAME: RRQ-
RideDriveLink
Filename: C:/TASC/Apps/
TASC-DriverDataCollect.xls
Has Field Names; YES
Range: DRIVERRIDELINKS

This appears to run BUT the weird issue is that the Transfer function
appears to import the contents of the defined area as it was BEFORE
the spreadsheet ran. Running the same macro a second time will bring
up the data before and so on. I have stopped the macro after the
RUNAPP command and the spradsheet contains what I would expect it to!
I am totally foxed by this, since there appears to be no obvious
reason. I have tried leaving the spreadsheet OPEN until after the
transfer, but that does not work either. Can anyone offer any
suggestions?

I do not want to get into using Access to edit and compile the
spreadsheets directly because finding the right file names to call is
quite tricky, and its much easier to use Excel for the editing.

Help much appreciated

John Baker

 

[Douglas J Steele]

Sounds to me as though your import step is happening before Excel has
changed the settings. Try putting in some sort of delay, or running it as
two separate macros.


"JHB" wrote in message

Hi:

I have an Excel sptreadsheet which consolidates data from a number of
spreadsheets and edits into a form that I can import into Access and
use for other processes -- BUT I HAVE A WEIRD PROBLEM.

To beigin with, you should be aware that the on opening, the
spreadsheet does its thing and then saves itself and closes Excel (and
itself). I am making this happen by executing the following commands
within a macro (run after emptying the target table):

RUNAPP: C:\Program Files(x86)\Microsoft
Office\Office\EXCEL.EXE "C:\TASC\apps\TASC-DriverDataCollect.xls"

TRANSFERSPREADSHEET: Transfer Type: Import
MicrosoftExcel 8-9
(Using Excel from Office 2000)
TABLENAME: RRQ-
RideDriveLink
Filename: C:/TASC/Apps/
TASC-DriverDataCollect.xls
Has Field Names; YES
Range: DRIVERRIDELINKS

This appears to run BUT the weird issue is that the Transfer function
appears to import the contents of the defined area as it was BEFORE
the spreadsheet ran. Running the same macro a second time will bring
up the data before and so on. I have stopped the macro after the
RUNAPP command and the spradsheet contains what I would expect it to!
I am totally foxed by this, since there appears to be no obvious
reason. I have tried leaving the spreadsheet OPEN until after the
transfer, but that does not work either. Can anyone offer any
suggestions?

I do not want to get into using Access to edit and compile the
spreadsheets directly because finding the right file names to call is
quite tricky, and its much easier to use Excel for the editing.

Help much appreciated

John Baker