Automating Excel to run an auto open macro in the sheet.

[Bill]

Is it possible to get an excell sheet to open and run a macro that is set in
the auto open part of the excel sheet.

When I automate a worksheet to open it seems to bypass the autoopen macro
imbeded in the sheet.
The autoopen macro takes two cells and pastes them on a worksheet.
The values of the cells is from a transferspreadsheet run from access.

As I can't get a copy paste to work from access on an Excell sheet i found
that transfering the info via transferspreadsheet was my only option.
When the excel sheet is opened manually the values are copied and pasted to
the correct ranges. When the sheet is opened via access automation this does
not occur.

Thanks in advance
Best Regards
Bil

 

[naive_1010]

Hi, Bill
i think it is possible. Firstly, you define your module or class module
to implement your logic. Then wrap those module macros into sheet or Workbook
level page. there are some event trigger for you to make decision when to
load macros.
Please let me in case you need any further clarification.

 

[Brian]

I use this scenario in a couple of apps, and I do this:

Create an Auto_Open macro in Workbook1. It opens Workbook2, changing things,
saves it as Workbook3, closes it then closes itself (Workbook1).

Then, I do my TransferText into Workbook2 and shell to Excel to run Workbook1:

Dim DataTemplate As String
Dim DataFile As String
DataTemplate = "C:\Workbook2.xls"
DataTemplate = "C:\Workbook3.xls"
OpenExcel = Shell("Excel.exe " & """" & DataTemplate & """", 0)
Do While Len(Dir(DataFile)) = 0
Loop

The last two lines just ensure that Workbook3 exists before moving on with
other code that may involve that workbook (such as uploading it to an FTP
server, etc.). Of course, this is relevant only if it does not exist between
sessions, so I archive it in some code following the loop above.

 

[Bill]

Hi There,
Your assisatnace would be really appreciated as I will be venturing into
unknown territory for "you define your module or class module ".

What I currently have in the excell workbook in a module is the following,
Option Explicit
Private module

Sub auto_open()

Call StartDialogue

End Sub
Sub StartDialogue()
Call Beginpart1

Call RVCalcOpen
End Sub
Sub Beginpart1()

Dim rngA As Range
Dim rng1 As Range

Worksheets(9).Visible = True
Set rngA = Worksheets(9).Range("B2") ' Bulk Density
rngA.Copy
Set rng1 = Worksheets(1).Range("B3")
Worksheets(1).Range(rng1.Address).PasteSpecial Paste:=xlPasteValues

Dim rngB As Range
Dim rng2 As Range

Worksheets(9).Visible = True
Set rngB = Worksheets(9).Range("C2") ' rate
rngB.Copy
Set rng2 = Worksheets(1).Range("B4")
Worksheets(1).Range(rng2.Address).PasteSpecial Paste:=xlPasteValues


End Sub

Sub RVCalcOpen()

'when the workbook opens the worksheet(1) is protected
'existing data and formulii are not allowed to be changed
'only certain cell can be altered to allow data entry
'all remaining worksheets are hidden

Worksheets(1).Visible = True
Worksheets(1).Activate

ActiveSheet.Unprotect

ActiveSheet.Range("b3:b4").Locked = False 'data entry cells
ActiveSheet.Range("b6").Locked = False
ActiveSheet.Range("f7").Locked = False ' variable change cells
ActiveSheet.Protect

Worksheets(2).Visible = False
Worksheets(3).Visible = False
Worksheets(4).Visible = False
Worksheets(5).Visible = False
Worksheets(6).Visible = False
Worksheets(7).Visible = False
Worksheets(8).Visible = False
Worksheets(9).Visible = False


Rem MsgBox "ok to here", vbOKOnly


End Sub

this code works if the workbook is opened manually.
The module that it sits in is called "Start"

In the Access form the vba to send the information to the excell workbook is
the following.

Private Function Fred()
'other variables already declared above

Dim oSheet As Excel.Worksheet

If Not IsNull(Me![Density]) And Not IsNull(Me![Rate]) Then

'update the forms records
Me.Refresh
'run query based on form ID to capture the Density and Rate values
'transfer these by transferspreadsheet to the xls sheet
'Update the two ranges on the xls sheet from vba in the sheet upon
open.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8,
"RVQuery", "C:\Database\ltd calcs sheet.xls", hasfieldnames:=True

'open spreadsheet

ExcelRunning = IsExcelRunning
If Not ExcelRunning Then
Set xlapp = CreateObject("Excel.Application")
Else
Set xlapp = GetObject(, "Excel.Application")
End If
Set xlbook = xlapp.Workbooks.Open("C:\Database\ltd calcs sheet.xls")
Set oSheet = xlapp.Worksheets("Quick RV Sizing")
xlapp.Visible = True



Else
'do nothing
End If


End Function

What happens is when this runs it bypasses the "autoopen" on the Excell
worksheet and no values are copied or pasted to the correct cells on the
worksheet.
If I try to move the copy paste to access to action it - access doesn't like
it.

Thanks in advance
Kind regards
Bill.

 

[Bill]

Thanks Brian,
Best regards
Bill

I use this scenario in a couple of apps, and I do this:

Create an Auto_Open macro in Workbook1. It opens Workbook2, changing things,
saves it as Workbook3, closes it then closes itself (Workbook1).

Then, I do my TransferText into Workbook2 and shell to Excel to run Workbook1:

Dim DataTemplate As String
Dim DataFile As String
DataTemplate = "C:\Workbook2.xls"
DataTemplate = "C:\Workbook3.xls"
OpenExcel = Shell("Excel.exe " & """" & DataTemplate & """", 0)
Do While Len(Dir(DataFile)) = 0
Loop

The last two lines just ensure that Workbook3 exists before moving on with
other code that may involve that workbook (such as uploading it to an FTP
server, etc.). Of course, this is relevant only if it does not exist between
sessions, so I archive it in some code following the loop above.