Hello, I am exporting Access 97 data to Excel (using TransferSpreadsheet
etc). Through an Access procedure I am then formatting the data in the
exported file. The problem is that when I run the code, I come up against an
error message on the following line of code:

xlApp.ActiveWorkbook.SaveAs ("filepath")

Elsewhere in the procedure I have avoided using any reference to ActiveCells
etc and the code runs ok, but I don't know an alternative to
ActiveWorkbook.SaveAs. Could anyone please advise? Many thanks in advance...

Tim Long

I have checked that the Excel 8.0 reference is present, have checked that
the file doesn't already exist and have tried clearing every Excel process
using task manager before running the code. All to no avail.

It would help to know the specific error. Also, is this a workbook you
opened from a saved copy, or is it a new workbook generated by your code?
Instead of ActiveWorkbook you should be able to refer to the workbook by
name, Workboooks(BookName), as long as you know (or set) the name. If opened
from a file, it should be the file name, e.g. Workbooks("saved file.xls"); if
created in code it is normally Workbooks("Book1") for the first one created,
etc.
--
- K Dales


"Tim Long" wrote:

Hello, I am exporting Access 97 data to Excel (using TransferSpreadsheet
etc). Through an Access procedure I am then formatting the data in the
exported file. The problem is that when I run the code, I come up against an
error message on the following line of code:

xlApp.ActiveWorkbook.SaveAs ("filepath")

Elsewhere in the procedure I have avoided using any reference to ActiveCells
etc and the code runs ok, but I don't know an alternative to
ActiveWorkbook.SaveAs. Could anyone please advise? Many thanks in advance...

Tim Long

I have checked that the Excel 8.0 reference is present, have checked that
the file doesn't already exist and have tried clearing every Excel process
using task manager before running the code. All to no avail.

Hello, the code in question is:

Set xlApp = CreateObject("Excel.Application")
xlApp.Workbooks.Add
xlApp.ActiveWorkbook.SaveAs "F:\Test_05_07_20.xls"
xlApp.ActiveWorkbook.Close
Set xlApp = Nothing

Later in the Access procedure, the Access data is exported to the
newly-created spreadsheet.

I have been getting a variety of errors. The latest one is Run-time error:
1004 The file could not be accessed. However, so far as I can see, there's no
problem with the file name, filename length, folder etc...

"K Dales" wrote:

It would help to know the specific error. Also, is this a workbook you
opened from a saved copy, or is it a new workbook generated by your code?
Instead of ActiveWorkbook you should be able to refer to the workbook by
name, Workboooks(BookName), as long as you know (or set) the name. If opened
from a file, it should be the file name, e.g. Workbooks("saved file.xls"); if
created in code it is normally Workbooks("Book1") for the first one created,
etc.
--
- K Dales


"Tim Long" wrote:

Hello, I am exporting Access 97 data to Excel (using TransferSpreadsheet
etc). Through an Access procedure I am then formatting the data in the
exported file. The problem is that when I run the code, I come up against an
error message on the following line of code:

xlApp.ActiveWorkbook.SaveAs ("filepath")

Elsewhere in the procedure I have avoided using any reference to ActiveCells
etc and the code runs ok, but I don't know an alternative to
ActiveWorkbook.SaveAs. Could anyone please advise? Many thanks in advance...

Tim Long

I have checked that the Excel 8.0 reference is present, have checked that
the file doesn't already exist and have tried clearing every Excel process
using task manager before running the code. All to no avail.

I have done a lot of Excel automation through Access - this is how I usually
handle similar situations:
Dim xlApp as Excel.Application
Dim xlBook as Excel.Workbook
Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Add()
xlBook.SaveAs "F:\Test_05_07_20.xls"
xlBook.Close
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing

As to the errors: I don't think they have to do with using ActiveWorkbook
(though I always prefer to refer to objects explicitly anyway, so I think the
above way of referencing the book is better). For one thing, you are not
getting consistent error messages - which implies that the condition
triggering the error is different each time. Also, the error you mention
would have nothing to do with how the workbook was referenced; it points to a
problem with file access. I do believe you when you say you have checked
this but that is still a least the error that Excel sees. I don't know why
that might be, the only thought I have is that timing might be an issue.
Perhaps for some reason the drive is not ready when you are trying to save
the book.
--
- K Dales


"Tim Long" wrote:

Hello, the code in question is:

Set xlApp = CreateObject("Excel.Application")
xlApp.Workbooks.Add
xlApp.ActiveWorkbook.SaveAs "F:\Test_05_07_20.xls"
xlApp.ActiveWorkbook.Close
Set xlApp = Nothing

Later in the Access procedure, the Access data is exported to the
newly-created spreadsheet.

I have been getting a variety of errors. The latest one is Run-time error:
1004 The file could not be accessed. However, so far as I can see, there's no
problem with the file name, filename length, folder etc...

"K Dales" wrote:

It would help to know the specific error. Also, is this a workbook you
opened from a saved copy, or is it a new workbook generated by your code?
Instead of ActiveWorkbook you should be able to refer to the workbook by
name, Workboooks(BookName), as long as you know (or set) the name. If opened
from a file, it should be the file name, e.g. Workbooks("saved file.xls"); if
created in code it is normally Workbooks("Book1") for the first one created,
etc.
--
- K Dales


"Tim Long" wrote:

Hello, I am exporting Access 97 data to Excel (using TransferSpreadsheet
etc). Through an Access procedure I am then formatting the data in the
exported file. The problem is that when I run the code, I come up against an
error message on the following line of code:

xlApp.ActiveWorkbook.SaveAs ("filepath")

Elsewhere in the procedure I have avoided using any reference to ActiveCells
etc and the code runs ok, but I don't know an alternative to
ActiveWorkbook.SaveAs. Could anyone please advise? Many thanks in advance...

Tim Long

I have checked that the Excel 8.0 reference is present, have checked that
the file doesn't already exist and have tried clearing every Excel process
using task manager before running the code. All to no avail.

Sorted! Many thanks!

"K Dales" wrote:

I have done a lot of Excel automation through Access - this is how I usually
handle similar situations:
Dim xlApp as Excel.Application
Dim xlBook as Excel.Workbook
Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Add()
xlBook.SaveAs "F:\Test_05_07_20.xls"
xlBook.Close
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing

As to the errors: I don't think they have to do with using ActiveWorkbook
(though I always prefer to refer to objects explicitly anyway, so I think the
above way of referencing the book is better). For one thing, you are not
getting consistent error messages - which implies that the condition
triggering the error is different each time. Also, the error you mention
would have nothing to do with how the workbook was referenced; it points to a
problem with file access. I do believe you when you say you have checked
this but that is still a least the error that Excel sees. I don't know why
that might be, the only thought I have is that timing might be an issue.
Perhaps for some reason the drive is not ready when you are trying to save
the book.
--
- K Dales


"Tim Long" wrote:

Hello, the code in question is:

Set xlApp = CreateObject("Excel.Application")
xlApp.Workbooks.Add
xlApp.ActiveWorkbook.SaveAs "F:\Test_05_07_20.xls"
xlApp.ActiveWorkbook.Close
Set xlApp = Nothing

Later in the Access procedure, the Access data is exported to the
newly-created spreadsheet.

I have been getting a variety of errors. The latest one is Run-time error:
1004 The file could not be accessed. However, so far as I can see, there's no
problem with the file name, filename length, folder etc...

"K Dales" wrote:

It would help to know the specific error. Also, is this a workbook you
opened from a saved copy, or is it a new workbook generated by your code?
Instead of ActiveWorkbook you should be able to refer to the workbook by
name, Workboooks(BookName), as long as you know (or set) the name. If opened
from a file, it should be the file name, e.g. Workbooks("saved file.xls"); if
created in code it is normally Workbooks("Book1") for the first one created,
etc.
--
- K Dales


"Tim Long" wrote:

Hello, I am exporting Access 97 data to Excel (using TransferSpreadsheet
etc). Through an Access procedure I am then formatting the data in the
exported file. The problem is that when I run the code, I come up against an
error message on the following line of code:

xlApp.ActiveWorkbook.SaveAs ("filepath")

Elsewhere in the procedure I have avoided using any reference to ActiveCells
etc and the code runs ok, but I don't know an alternative to
ActiveWorkbook.SaveAs. Could anyone please advise? Many thanks in advance...

Tim Long

I have checked that the Excel 8.0 reference is present, have checked that
the file doesn't already exist and have tried clearing every Excel process
using task manager before running the code. All to no avail.

In automation, I would avoid using Activeworkbook (in fact, it is best to
avoid it without automation <g) by setting a workbook object when you open
the file or create it

Something like

xlWB = xlApp.Workbooks.Open "myFile.xls"

or

xlWB = xlApp.Worksbooks.Add

That way you always have a handle to that workbook.

Thenn when saving, just use

xlWB..SaveAs "filepath"


--
HTH

Bob Phillips

"Tim Long" wrote in message
...
Hello, I am exporting Access 97 data to Excel (using TransferSpreadsheet
etc). Through an Access procedure I am then formatting the data in the
exported file. The problem is that when I run the code, I come up against
an
error message on the following line of code:

xlApp.ActiveWorkbook.SaveAs ("filepath")

Elsewhere in the procedure I have avoided using any reference to
ActiveCells
etc and the code runs ok, but I don't know an alternative to
ActiveWorkbook.SaveAs. Could anyone please advise? Many thanks in
advance...

Tim Long

I have checked that the Excel 8.0 reference is present, have checked that
the file doesn't already exist and have tried clearing every Excel process
using task manager before running the code. All to no avail.