UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Open Excel File With Automation V Taskbar, Access 2016    
 
   
elsuwi
post Aug 23 2017, 03:26 PM
Post#1



Posts: 163
Joined: 6-December 03
From: Nebraska, USA


When I open a new Excel file via the start menu or taskbar, I have custom macros available to me. Digging around a bit it looks like there is an xlam file in the XLSTART directory.

However when I open an xlsm file by double clicking on it, the macros are not available. If I go back and open Excel then use File Open to open the same file the macros are available.

Is there a way to open the file via automation so the custom macros are available?
Go to the top of the page
 
ADezii
post Aug 25 2017, 01:01 PM
Post#2



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
Is there a way to open the file via automation so the custom macros are available?

Yes there is.
  1. The following Code, using Early Binding, will:
    1. Open Test.xlsm in the C:\Test\ Folder.
    2. Activate Sheet1.
    3. Execute Macro1 which Formats a specific Range on Sheet1.
    4. Saves then Closes the Workbook.
    5. Performs some Clean Up chores.
  2. Code Definition:
    CODE
    Dim appExcel As Excel.Application
    Dim wrk As Excel.Workbook
    Dim wks As Excel.Worksheet

    Set appExcel = New Excel.Application
        appExcel.Visible = True
    Set wrk = appExcel.Workbooks.Open("C:\Test\Test.xlsm")

    Set wks = wrk.Worksheets("Sheet1")
        wks.Activate

    appExcel.Run "'C:\Test\Test.xlsm'!Macro1"

    wrk.Save
    wrk.Close

    Set wks = Nothing
    Set wrk = Nothing

    appExcel.Quit
    Set appExcel = Nothing
  3. Good Luck with your Project.
Go to the top of the page
 
elsuwi
post Aug 25 2017, 01:25 PM
Post#3



Posts: 163
Joined: 6-December 03
From: Nebraska, USA


Is it possible to reference a file that is already open?
Go to the top of the page
 
ADezii
post Aug 25 2017, 04:52 PM
Post#4



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


If you are absolutely certain that an Instance of Excel is running, and the Workbook C:\Test\Test.xlsm is active within this Instance, then the following Code will do the trick as long as Marco1 is defined in C:\Test\Test.xlsm. If you are not sure of the above, additional Code will be required.
CODE
Dim objExcel As Object
Dim wrk As Object
Dim wks As Object

Set objExcel = GetObject(, "Excel.Application")      'Retrieve the running Instance of Excel

Set wrk = objExcel.Workbooks(1)      'C:\Test\Test.xlsm

Set wks = wrk.Worksheets("Sheet1")   'Sheet1 in C:\Test\Test.xlsm
    wks.Activate

objExcel.Run "Macro1"      'Run Macro from Instance of Excel

wrk.Save
wrk.Close

Set wks = Nothing
Set wrk = Nothing

objExcel.Quit
Set objExcel = Nothing
Go to the top of the page
 
elsuwi
post Sep 5 2017, 05:03 PM
Post#5



Posts: 163
Joined: 6-December 03
From: Nebraska, USA


Perfect. Thanks!
Go to the top of the page
 
ADezii
post Sep 6 2017, 06:48 AM
Post#6



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


yw.gif
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    13th December 2017 - 03:56 PM