OLE Automation of Excel from Access 7.0 
Author Message
 OLE Automation of Excel from Access 7.0

I hope someone here can help me.

I am developing a database for a company and attempting to fully
automate Excel from Access 7.0.  The code works in Excel, but when
transferred into the related code for Access. It doesn't work.

Here is the problem.  When I finish manipulating the data in Excel, I
need to save the file to the same name without prompting to overwrite
the existing file.  This can be done by using the save method.  However,
if the file doesn't exist (which it may not), the program will respond
with an error.  For that reason, it is neccessary I use the CloseWindow
method or SaveAs method where I can specify a filename.  However, both
of these prompt to overwrite if the file exists.  I have tried to kill
the filename in advance with no luck.

Here is the code in Excel:

Sub Close_Window ()

Application.DisplayAlerts = False
ActiveWindow.Close True, "salerept.xls"

End Sub

***The above code was given to me by the "Support Engineer" at Microsoft
for Excel***

This is the code as I wrote it in Access:

Private Sub Command0_Click()

******Miscellaneuous Declerations*******

Dim xlObject As Object

******Miscellany*******

' Attempt at OLE Automation of Microsoft Excel

  Set xlObject = CreateObject("excel.application")
  xlObject.Visible = True
  xlObject.Workbooks.Add _
    ("salerept.xls")

******Miscellany*******

' This is the code that doesn't work properly.

  xlObject.Application.DisplayAlerts = False
  xlObject.ActiveWorkbook.Close True, "salerept.xls"

Exit Sub

Err_Form_Activate:
    MsgBox Err.Description

End Sub

If someone could PLEASE tell me how to do this as soon as possible, the
project is already a week delayed...

Thanks,
Mike



Tue, 20 Jul 1999 03:00:00 GMT  
 OLE Automation of Excel from Access 7.0

Quote:

> I hope someone here can help me.

> I am developing a database for a company and attempting to fully
> automate Excel from Access 7.0.  The code works in Excel, but when
> transferred into the related code for Access. It doesn't work.

> Here is the problem.  When I finish manipulating the data in Excel, I
> need to save the file to the same name without prompting to overwrite
> the existing file.  This can be done by using the save method.  However,
> if the file doesn't exist (which it may not), the program will respond
> with an error.  For that reason, it is neccessary I use the CloseWindow
> method or SaveAs method where I can specify a filename.  However, both
> of these prompt to overwrite if the file exists.  I have tried to kill
> the filename in advance with no luck.

> Here is the code in Excel:

> Sub Close_Window ()

> Application.DisplayAlerts = False
> ActiveWindow.Close True, "salerept.xls"

> End Sub

> ***The above code was given to me by the "Support Engineer" at Microsoft
> for Excel***

> This is the code as I wrote it in Access:

> Private Sub Command0_Click()

> ******Miscellaneuous Declerations*******

> Dim xlObject As Object

> ******Miscellany*******

> ' Attempt at OLE Automation of Microsoft Excel

>   Set xlObject = CreateObject("excel.application")
>   xlObject.Visible = True
>   xlObject.Workbooks.Add _
>     ("salerept.xls")

> ******Miscellany*******

> ' This is the code that doesn't work properly.

>   xlObject.Application.DisplayAlerts = False
>   xlObject.ActiveWorkbook.Close True, "salerept.xls"

> Exit Sub

> Err_Form_Activate:
>     MsgBox Err.Description

> End Sub

> If someone could PLEASE tell me how to do this as soon as possible, the
> project is already a week delayed...

> Thanks,
> Mike

Before you run the OLE part check if the file exists and delete it if it
does.  Then ste the Excel Object, add the workbook, perform the SaveAs,
amd then close the workbook.

ie:

if fFileExists(sFileName) then kill sFileName

set vExcel = CreateObject("Excel.Sheet")
with vAxcel.Application
        .Workbooks.Add
        .Workbooks.SaveAs FileName:= sNewFileName
        .ActiveWorkbook.close
end with
set vExcel = nothing

Hope this helps.



Tue, 20 Jul 1999 03:00:00 GMT  
 OLE Automation of Excel from Access 7.0

Quote:

> I hope someone here can help me.

> I am developing a database for a company and attempting to fully
> automate Excel from Access 7.0.  The code works in Excel, but when
> transferred into the related code for Access. It doesn't work.

> Here is the problem.  When I finish manipulating the data in Excel, I
> need to save the file to the same name without prompting to overwrite
> the existing file.  This can be done by using the save method.  However,
> if the file doesn't exist (which it may not), the program will respond
> with an error.  For that reason, it is neccessary I use the CloseWindow
> method or SaveAs method where I can specify a filename.  However, both
> of these prompt to overwrite if the file exists.  I have tried to kill
> the filename in advance with no luck.

> Here is the code in Excel:

> Sub Close_Window ()

> Application.DisplayAlerts = False
> ActiveWindow.Close True, "salerept.xls"

> End Sub

> ***The above code was given to me by the "Support Engineer" at Microsoft
> for Excel***

> This is the code as I wrote it in Access:

> Private Sub Command0_Click()

> ******Miscellaneuous Declerations*******

> Dim xlObject As Object

> ******Miscellany*******

> ' Attempt at OLE Automation of Microsoft Excel

>   Set xlObject = CreateObject("excel.application")
>   xlObject.Visible = True
>   xlObject.Workbooks.Add _
>     ("salerept.xls")

> ******Miscellany*******

> ' This is the code that doesn't work properly.

>   xlObject.Application.DisplayAlerts = False
>   xlObject.ActiveWorkbook.Close True, "salerept.xls"

> Exit Sub

> Err_Form_Activate:
>     MsgBox Err.Description

> End Sub

> If someone could PLEASE tell me how to do this as soon as possible, the
> project is already a week delayed...

> Thanks,
> Mike

Before you run the OLE part check if the file exists and delete it if it
does.  Then ste the Excel Object, add the workbook, perform the SaveAs,
amd then close the workbook.

ie:

if fFileExists(sFileName) then kill sFileName

set vExcel = CreateObject("Excel.Sheet")
with vAxcel.Application
        .Workbooks.Add
        .Workbooks.SaveAs FileName:= sNewFileName
        .ActiveWorkbook.close
end with
set vExcel = nothing

Hope this helps.



Tue, 20 Jul 1999 03:00:00 GMT  
 OLE Automation of Excel from Access 7.0

Quote:

> I hope someone here can help me.

> I am developing a database for a company and attempting to fully
> automate Excel from Access 7.0.  The code works in Excel, but when
> transferred into the related code for Access. It doesn't work.

> Here is the problem.  When I finish manipulating the data in Excel, I
> need to save the file to the same name without prompting to overwrite
> the existing file.  This can be done by using the save method.  However,
> if the file doesn't exist (which it may not), the program will respond
> with an error.  For that reason, it is neccessary I use the CloseWindow
> method or SaveAs method where I can specify a filename.  However, both
> of these prompt to overwrite if the file exists.  I have tried to kill
> the filename in advance with no luck.

> Here is the code in Excel:

> Sub Close_Window ()

> Application.DisplayAlerts = False
> ActiveWindow.Close True, "salerept.xls"

> End Sub

> ***The above code was given to me by the "Support Engineer" at Microsoft
> for Excel***

> This is the code as I wrote it in Access:

> Private Sub Command0_Click()

> ******Miscellaneuous Declerations*******

> Dim xlObject As Object

> ******Miscellany*******

> ' Attempt at OLE Automation of Microsoft Excel

>   Set xlObject = CreateObject("excel.application")
>   xlObject.Visible = True
>   xlObject.Workbooks.Add _
>     ("salerept.xls")

> ******Miscellany*******

> ' This is the code that doesn't work properly.

>   xlObject.Application.DisplayAlerts = False
>   xlObject.ActiveWorkbook.Close True, "salerept.xls"

> Exit Sub

> Err_Form_Activate:
>     MsgBox Err.Description

> End Sub

> If someone could PLEASE tell me how to do this as soon as possible, the
> project is already a week delayed...

> Thanks,
> Mike

Before you run the OLE part check if the file exists and delete it if it
does.  Then ste the Excel Object, add the workbook, perform the SaveAs,
amd then close the workbook.

ie:

if fFileExists(sFileName) then kill sFileName

set vExcel = CreateObject("Excel.Sheet")
with vAxcel.Application
        .Workbooks.Add
        .Workbooks.SaveAs FileName:= sNewFileName
        .ActiveWorkbook.close
end with
set vExcel = nothing

Hope this helps.



Tue, 20 Jul 1999 03:00:00 GMT  
 OLE Automation of Excel from Access 7.0

Mike ,
Instead of:

Quote:
> if fFileExists(sFileName) then kill sFileName

you can use:
If Len(Dir$(sFileName))>0 then kill sFileName
But only if sFileName is the full path.
Scott Nation


Thu, 22 Jul 1999 03:00:00 GMT  
 OLE Automation of Excel from Access 7.0

I really appreciate all your help in this issue, but it there a simpler
way...because I have no idea what you are saying.  I am for all
practical purposes lost.  This is something I do in my sparetime at
college.  I have learned VBA and access on my own and you have lost me
with your explanation of how to create the function.  Any further
assistance would be greatly appreciated.

Thanks in advance,
Stephen M. Chittenden



Fri, 23 Jul 1999 03:00:00 GMT  
 OLE Automation of Excel from Access 7.0

Quote:

> if fFileExists(sFileName) then kill sFileName

> This line results in an error saying fFileExists is an undefined
> function....

> I really don't know a lot about Visual Basic or Access, I've sort of
> learned as I went along.

> Mike

>     ---------------------------------------------------------------

> if fFileExists(sFileName) then kill sFileName

> This line results in an error saying fFileExists is an undefined
> function....

> I really don't know a lot about Visual Basic or Access, I've sort of
> learned as I went along.

> Mike

You need to create the fFileExists function.  If you want you can
declare an API function in a module in the declarations part as:

Declare Function fFileExists Lib "MSAU200.DLL" Alias "#5" (ByVal sSrc As
String) As Integer

Then you can call the function from anywhere.

The above function is for Access 2.0.  I think the DLL for Access 7.0 is
MSAU300.DLL.  Check your Access directory for the correct name.

John



Fri, 23 Jul 1999 03:00:00 GMT  
 OLE Automation of Excel from Access 7.0

Mike,

Application.DisplayAlerts won't work from outside Excel.  The following is
an excerpt from the DisplayAlerts Property help in Excel.

"This property is not effective from outside Microsoft Excel. For example,
you can set this property to False from another application (using OLE
Automation), but Microsoft Excel will immediately reset it to True. Because
OLE Automation calls are processed as single-line macros and Microsoft
Excel resets this property whenever a macro stops running, the property is
reset immediately after you set it."

I've gotten around this in the past by creating a procedure in Excel that
contains the DisplayAlerts command, then calling the procedure from Access
using an "xlObject.Run" command.  If you want to know more about this,
check out the online help in Excel under "DisplayAlerts" and "Run"

Good Luck,

Todd Penland



Quote:
> ******Miscellany*******

> ' This is the code that doesn't work properly.

>   xlObject.Application.DisplayAlerts = False
>   xlObject.ActiveWorkbook.Close True, "salerept.xls"



Mon, 26 Jul 1999 03:00:00 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. OLE Access Excel (Datenimport von Excel in Access - muss nicht OLE sein)

2. Accessing Excel 7.0 from my vb4.0.16-bit through OLE

3. Accessing empty Excel cells via OLE automation from VB 3.0

4. VB5 OLE Automation: MS Access Data into Excel

5. OLE Automation Error Experienced with Word 7.0 not with Word 6.0

6. OLE Automation VB5.0 and Word 7.0 for win95

7. OLE Automation Error Experienced with Word 7.0 not with Word 6.0

8. OLE Automation Error experienced with Word 7.0 not with Word 6.0

9. Need Help with OLE Automation and PPT 7.0

10. HELP, Ole, SQL and Excel 7.0

11. Help: 16-bit VB4, OLE, Excel 3.0, 4.0, 5.0, 5.0c, 7.0 and 8.0

12. Access to Excel Automation - Release Excel Object

 

 
Powered by phpBB® Forum Software