Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run code from another workbook
I am trying to do as the Subject says.
I found a site that has info on that. <******* Sub CallCodeFromAnotherWorkbook() Application.Run "AnotherWorkbook.xls"!OtherMacro End Sub To replicate: 1.Open both the example workbooks 2.Press the button. 3.You will then see a message box saying that the other code is being run. 4.The Syntax for Application.Run is: "WorkbookName.xls!MacroName" 5.Using this tip, you can run all the macros from one workbook using another workbook. <******* I down loaded their two books and after correcting what I guess was a typo, it did work. Everything I've tried with my Books fails. I have three Books and I am try to call two macros from other books from the book long. The books a long Book_A Book_AA Macro in the book long is: Sub BooK_A_Book_AA_Macro_Call() Application.Run "Book_A.xls!AbookToLong" Application.Run "Book_AA.xls!AAbookToLong" End Sub The Macro in Book_A is: Sub AbookToLong() The Macro in Book_AA is: Sub AAbookToLong() Notice the extensions are .xls (saved as 97-2003). The download example came as .xls. I have tried saving everything as Excel Macro-Enabled and have the .xlsm extension but nothing works. No error number, just yellows out the first line of Application.Run...etc. I have tried calling only one macro and that does not work either. Seems to like it should work with Excel 2010. Do you see anything I'm doing wrong? Thanks. Howard |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run code from another workbook
I was unable to copy the macro from the site so I typed it in here and I made a typo.
This is NOT the typo I mentioned in previous post. Sub CallCodeFromAnotherWorkbook() Application.Run "AnotherWorkbook.xls"!OtherMacro End Sub Miss placed the second " Sub CallCodeFromAnotherWorkbook() Application.Run "AnotherWorkbook.xls!OtherMacro" End Sub Howard |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run code from another workbook
Hi Howard,
Am Mon, 17 Feb 2014 03:24:35 -0800 (PST) schrieb L. Howard: I have tried saving everything as Excel Macro-Enabled and have the .xlsm extension but nothing works. No error number, just yellows out the first line of Application.Run...etc. for me it works fine. Could there be trailing spaces into the workbook name? Try: Application.Run "'Book_A.xls'!AbookToLong" Application.Run "'Book_AA.xls'!AAbookToLong" with single quotes around the name Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run code from another workbook
On Monday, February 17, 2014 3:39:44 AM UTC-8, Claus Busch wrote:
Hi Howard, Am Mon, 17 Feb 2014 03:24:35 -0800 (PST) schrieb L. Howard: I have tried saving everything as Excel Macro-Enabled and have the .xlsm extension but nothing works. No error number, just yellows out the first line of Application.Run...etc. for me it works fine. Could there be trailing spaces into the workbook name? Try: Application.Run "'Book_A.xls'!AbookToLong" Application.Run "'Book_AA.xls'!AAbookToLong" with single quotes around the name Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 That is encouraging. Could you put your example on the Skye thing, time permitting. May help me trouble shoot my problem. Meanwhile I will give things another doing over. Thanks. Howard |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run code from another workbook
Hi Howard,
Am Mon, 17 Feb 2014 04:30:22 -0800 (PST) schrieb L. Howard: Could you put your example on the Skye thing, time permitting. have a look: https://skydrive.live.com/#cid=9378A...121822A3%21326 All three books are there Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run code from another workbook
https://skydrive.live.com/#cid=9378A...121822A3%21326 All three books are there Regards Claus B. -- Yes, that helped and that now works. I moved everything into standard modules, where it probably should have been to start with. Then myRng stopped copying to the sheet Long, no error, just no data transfer, except the two columns near the bottom of the code. So, since those two columns are still copying to Long I figured I needed to add wksSource to the Set myRng line. But that line now errors out with an object variable not set. It works with the columns down below, is it different with an array? Howard Sub AbookToLong() Dim myRng As Range, MyRng1 As Range Dim rngC As Range Dim i As Long Dim myArr() As Variant Dim wksSource As Worksheet, wksTarget As Worksheet Dim wkbSource As Workbook, wkbTarget As Workbook Dim rngSource As Range, rngTarget As Range '/*** Set myRng = wksSource.Range("AV2,AW4,X20,V20,AN2,AO4,X8,AF2,AG 4") 'Set myRng = Range("AV2,AW4,X20,V20,AN2,AO4,X8,AF2,AG4") '/*** Set wkbSource = Workbooks("Book_A.xlsm") Set wkbTarget = Workbooks("long.xlsm") Set wksSource = wkbSource.Sheets("Sheet1") Set wksTarget = wkbTarget.Sheets("Sheet1") Application.ScreenUpdating = False For Each rngC In myRng ReDim Preserve myArr(myRng.Cells.Count - 1) myArr(i) = rngC i = i + 1 Next With wksSource wksTarget.Range("M2").Resize(columnsize:=myRng.Cel ls.Count) = myArr End With wksSource.Range("C7:C18").Copy wksTarget.Range("X2").PasteSpecial Transpose:=True wksSource.Range("C33:C50").Copy wksTarget.Range("AJ2").PasteSpecial Transpose:=True Application.ScreenUpdating = False End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run code from another workbook
Hi Howard,
Am Mon, 17 Feb 2014 05:55:19 -0800 (PST) schrieb L. Howard: Set myRng = wksSource.Range("AV2,AW4,X20,V20,AN2,AO4,X8,AF2,AG 4") 'Set myRng = Range("AV2,AW4,X20,V20,AN2,AO4,X8,AF2,AG4") '/*** Set wkbSource = Workbooks("Book_A.xlsm") Set wkbTarget = Workbooks("long.xlsm") Set wksSource = wkbSource.Sheets("Sheet1") Set wksTarget = wkbTarget.Sheets("Sheet1") you have to set the workbooks and the sheets before setting the range: Set wkbSource = Workbooks("Book_A.xlsm") Set wkbTarget = Workbooks("long.xlsm") Set wksSource = wkbSource.Sheets("Sheet1") Set wksTarget = wkbTarget.Sheets("Sheet1") Set myRng = wksSource.Range("AV2,AW4,X20,V20,AN2,AO4,X8,AF2,AG 4") Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run code from another workbook
On Monday, February 17, 2014 6:09:28 AM UTC-8, Claus Busch wrote:
Hi Howard, Am Mon, 17 Feb 2014 05:55:19 -0800 (PST) schrieb L. Howard: Set myRng = wksSource.Range("AV2,AW4,X20,V20,AN2,AO4,X8,AF2,AG 4") 'Set myRng = Range("AV2,AW4,X20,V20,AN2,AO4,X8,AF2,AG4") '/*** Set wkbSource = Workbooks("Book_A.xlsm") Set wkbTarget = Workbooks("long.xlsm") Set wksSource = wkbSource.Sheets("Sheet1") Set wksTarget = wkbTarget.Sheets("Sheet1") you have to set the workbooks and the sheets before setting the range: Set wkbSource = Workbooks("Book_A.xlsm") Set wkbTarget = Workbooks("long.xlsm") Set wksSource = wkbSource.Sheets("Sheet1") Set wksTarget = wkbTarget.Sheets("Sheet1") Set myRng = wksSource.Range("AV2,AW4,X20,V20,AN2,AO4,X8,AF2,AG 4") Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 Aaaaarrgh!! You know, I knew that! Dang, too absorbed with the other pesky task . Thanks. Howard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA code for pasting links -- modifying RDB code for summary workbook | Excel Programming | |||
How do I call up a line of code that references a cell/range in theactive workbook workbook where I am running my macro from? | Excel Programming | |||
Code to change code in a sheet and workbook module | Excel Programming | |||
run code on opening workbook and apply code to certain sheets | Excel Programming | |||
Code in one workbook to call code in another XL file | Excel Programming |