Calling Macro In Another Workbook And Carrying Some Variables Back And Forth
May 22, 2013
I have a macro running in a workbook that gathers some data (a date, a string and a few arrays). Towards the end of this macro, I need it to open another workbook and run a macro that sits in this other workbook, using the data from the first workbook. I then need it to return some results (several integers) back to the first macro to be pasted into the first workbook.
I gather that I can't use 'Call' as the second macro is in another workbook.
I've found that I can use Application.Run but I'm unsure how to carry variables back and forth using this.
How to move the variables between macros / workbooks using the Application.Run option, or maybe another way of doing things?
I have a set of eight files, each of which is refreshed daily through a macro. I wanted to set up a master macro in a separate file that would open up each of the eight in turn, calls its refresh macro (which saves and closes the workbook), and then open up the next one, etc. I've searched the board and Help on this subject, but I keep getting an error:
Sub UpdateCSHoldReports() ' A sub to update all the US CS Hold Reports
' First, change the directory to the one that holds the files
ChDir "s:Marketing_ReportsCustomer Service2006CS Hold ReportsUSA"
' Next, open each file in turn, and run its refresh macro ' The refresh macro saves and closes the file automatically
Workbooks.Open ("VERIFICATION HOLDS 1000 PLUS.xls") Workbooks("VERIFICATION HOLDS 1000 PLUS.xls").Activate ActiveWorkbook.Sheets(1).Activate Application.Run ("VERIFICATION HOLDS 1000 PLUS.xls!V1000Refresh") End Sub
I'm only working on one file right now; I'll add the others when I get this one going. The error message I get is "1004" "The macro VERIFICATION HOLDS 1000 PLUS.xls!V1000Refresh cannot be found." It occurs when the Application.Run statement attempts to execute.
What obvious thing am I missing here? I have checked all the spellings, etc., so it's not something like that.
I have been combining multiple macro's into one large Macro and after research, it seems that only the call function works without any hitches.
Is there a way to use the call feature in one workbook while the macro's themselves, 5 or 6 of them, are in another workbook that is closed?
All users would have access to both workbooks.
The reason I am trying this is because I do not want all the other users to have to choose between multiple macro's and some of my colleagues like to use a radio button to link to the workbook. so it needs to have one file in it...
The below thing has been driving me up a wall for 2 days now. I know the fix has to do with calling the variables, but I can't figure out the exact code.
Sub Parsing_Logic() Dim IBG_URL As String Dim A As String A = Mid(IBG_URL, Application.WorksheetFunction.Find("/200", IBGURL), 8) Dim B As String B = Left(IBG_URL, Application.WorksheetFunction.Find("/200", IBGURL)) Dim C As String C = Right(IBG_URL, Len(IBG_URL) - (Len(A) + Len(B))) Dim D As String D = Left(C, Application.WorksheetFunction.Find(".200", C)) End Sub
Function IBGLink(IBG_URL As String, Formatted_Date As String) If Application.WorksheetFunction.IsErr(A) Then IBGLink = (D & Formatted_Date) Else: IBGLink = (B & Formatted_Date & D & Formatted_Date) End If
I would like to know if it is possible to display a form using a variable containing the form name rather than the form name itself, I have tried everything I can think of and cannot make it work. I know I can use CASE SELECT but I have a large number of forms involved and would like to use a variable name if possible.
problems with the syntax involved in calling one subroutine from within another. I want to pass two variables calculated in a 'main' sub into the parentheses of another I am calling (see pseudocode below). When I pass one variable, everything works fine, however, when I try to include another, I get an error message - something about expecting an "=" in the syntax, which is clearly not correct.
Can a Function give two or more output variables. e.g.
Sub a() x = 5 result = Y(x) End Sub
Function Y (x As Integer) As Integer Dim B B = ... * x Y = ... * B
this will give back Y as a result. But if I want to get 2 or more output variables (let's say I need to get also B into sub) from one function, how should I do that? I need this because function works with large matrix and I want to extract some values appeared in between.
I have a query where the correct name is mixed up by each single click of a button and finally the mixed characters reverts back to correct name. I will give the examples below.
1) Beautiful --> next click of a button 2) utifulBea --> next click of a button 3) Beautiful --> the final output will be back to same name.
I am trying to use VB to vlookup between to workbooks 1. Make active workbook WBK1 2. Make workbook being open WBK2 3. Copy and Paste between WBK1 and WBK2 4. Have a vlookup in WBK1 and bring in the values from WBK2 5. Close WKB2 6. Copy, Paste, and transpose values in wkb1 within wkb1
The script works fine until it reaches the vlookup step. I have used the vlookup by itself without the copy and paste code successfully but when I combine the two it provides me with the error 9. Subscript out of range.
I have a hundred or more spreadsheets that I will be applying a utility to. I want to use one button on each sheet to call a form in the utility workbook. That form will have buttons that call individual parts of the utility.
I tried application.run("utility.xlsm!frmStart"). No dice.
I also tried: "utility.xlsm!frmStart". Same result.
How can I overcome this.
The reason I want my forms in a different workbook is the difficulty with maintenance and installation.
I have designed a multipage userform which is executed through a toolbar button in my Excel. How can I access this Userform from another workbook with a different file name?
I have tried by using the tool>References and checking the VBAProject in the original workbook, however, when I click on the toolbar button in Excel (to activate the userform dialog box), the error message "A document with the filename already exists. Cannot open two documents with same name." Problem is....the filenames are not the same.
I have the following code in a module that I would like to initiate when the workbook opens:
Code: Private Declare Function GetComputerName Lib "kernel32" _ Alias "GetComputerNameA" ( _ ByVal lpBuffer As String, _ ByRef nSize As Long) As Long
Public Property Get ComputerName() As String
[Code] ........
In a worksheet, I have =compname() in cell A1 and =hdserialnumber() in A2. What is the trick to make the values in these cells appear as soon as the worksheet opens?
I want to copy a named range from an external workbook. Currently I am using some VBA copied from a recorded macro that first opend the 2nd workbook and then selects the named range, changes back to the original workbook and drops the copied range into the active workbook.
I would like to find a way to get around having to open the reference workbook, and instead simply point to the proper workbook name and named range.
I have a workbook (Program_1.xls) which references another workbook (Program_2.xls). I want to close Program_1.xls using code in Program_2.xls. When the Program_1.xls is closed, the code stops.
Program_1.xls code
Sub Program_1() MsgBox "This is program #1" Application.Run "Program2.xls!Program_2" End Sub
Program_2.xls code
Sub Program_2() Source = "C:Documents and SettingsCohenMy Documentsprogram3.xls" target = "C:Documents and SettingsCohenMy Documentsprogram1.xls" Workbooks("Program1.xls").Close savechanges:=False ' ---> code halts here MsgBox "This is program #2" FileCopy Source, target End Sub
How do I get the final 2 lines of code to execute?
What i have at the moment is a module that contains code where i call a different module that i use as a procedure.
Module1
Code: If Sheet1.Range("C4").Value < Sheet1.Range("A2").Value Then If Sheet1.Range("K4") = "" Then MsgBox "Please check 06:00 tasks not done yet!" Cell = "Range(" & Chr(34) & "F4" & Chr(34) & ")" If Sheet1.Range("C4") + 0.042 < Sheet1.Range("A2") Then Run "EmailProSheet" End If End If End If
EmailProSheet is what i call but now i want to use the variable "Cell" in the procedure as well?
Module3
Code: MsgBox Cell Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) On Error Resume Next
[Code]........
As it is now everything is working fine but does not return a value in "Cell" if the procedure is called. Is there another way?
I am currently using this formula: =IF(AND(D3=$H$1,E3="Win"),1,0), where E3 can also equal "Loss" or "Draw", and the cells D3, E3, and F3 are populated by data validation lists containing various criteria. I have seven total Win/Loss/Draw columns, and in the first row the formula works perfectly when you change one of the cells in D3:F3. But when I copy the formula to create a second row, it no longer works!
I have created an XLA using a blank workbook which has a macro (called CompareMan) I have written within it.
I have written a utility (called INSTALLMAN) to install this add in.
When I run the install it creates and activates the add in OK.
The install also creates a toolbar with a button to trigger the macro. Problem is, when I click the newly created button, no matter which workbook I am currently in I get a message ...
"The macro INSTALLMAN.xls!CompareMan cannot be found".
So my question is, how do I get my button to trigger the macro in my add in?
I am trying to call another macro, but instead of using the name of the macro, I have it in a variable. I am getting an error that says "Compile Error: Expected sub, Function, or Property." Can you call a macro that is in a variable? I basically have a bunch of macros created. I also have a list of the name of those macros on a worksheet in Excel. I want excel to start at the top of the list in excel and run the macro, then move down and run the next macro, and so on. Below is my code.
Sub Commercial_2005() Dim Macroname As String Workbooks("bleeg.xls").Activate Worksheets("CommercialList").Select Cells.Range("a1").Select While ActiveCell.Value <> "" Macroname = ActiveCell.Value Workbooks("copy of recast_Report_v2.xls").Activate Call Macroname Workbooks("bleeg.xls").Activate Worksheets("CommercialList").Select ActiveCell.Offset(1, 0).Select Wend End Sub
I have a macro that unprotects a worksheet copies data from that worksheet and pastes it into a newly created and saved workbook. However I have two problems
1) I'm attempting to copy the data from the first workbook by selecting all cells and then when switching to the new workbook, select all cells and the paste special (values) then paste special (formats). This works when recording the macro but when I attempt to run it it bombs out.
2) How do I get the macro to switch back to the workbook it got its data from. The name of the source workbook varies and there may be from 2 to 15 workbooks open. Is there a previous active workbook command? or do I need to create some kkind of temporary value to hold the file name of the source workbook.
I am having some trouble writing a code for a macro that moves data between documents.
The document that I transfer data from is an excel extract from a survey, so everytime I have to run the macro, it is with a new document with a new name.
I can get so far as to move the first cell information into the other specified workbook, but I cannot get the macro to return to the the first document (the one where the name changes with every use).
I'm writing some code in a "control" Excel file that will open a series of other files and successively run some processes. Each of these files has its own unique "refresh" macros, with parameters that vary from file to file.
How do I put code in my "control" file that will, when it opens each of these other files, run the "refresh" macro that is contained within them, and not the "control" file itself?
I have a workbook with two macros that the user can run individually. They unfortunately take a fair amount of time to complete (approx 1 hour each) so I implemented some timing related code that at the end of the macro completion, it notifies them with a msgBox indicating how long it took to run. With that said, I would like to allow the user to run a macro that simply calls the other two so that they can kick it off at the end of the day and they both will be finished by morning. I created a new macro that simply makes a call to each of the other macros individually. The problem is, my msgBox that displays after the first macro completes, waits for "OK" acknowledgement which defeats the whole purpose of being able to run them both via one macro. I have tried to include Application.DisplayAlerts = False prior to calling the first macro but it is not working. Here is an example of what I am trying to do.
Sub runAllMacros() Application.DisplayAlerts = False Call compileBookData Call compileLaborData Application.DisplayAlerts = True MsgBox "All Rollup Macros have completed execution."
I have a workbook with a 2 macros "PopulateSheetlist" and "SaveEditedversion"
Normally they are each button operated, and prompt the user with vbYesNo style options. I wanted to write a Macro, say "Macroautomatewkbk that would automatically answer the prompts.
Sub Macroautomatewkbk ()
Call PopulateSheetlist
' Always answer YES or OK to any prompts this macro may offer
Call SaveEditedversion
' Answer No for the 1st prompt and YES for the 2nd prompt
End Sub
MsgBox "Both macros "PopulateSheetlist" and "SaveEditedversion"
' Some error handler here, don't know how this should work exactly. Could any one please explain how to fill in code in the commented sections in the above sample code?
Edit: I tried recording a macro to do the above, but it only showed the zooming and scrolling that occurred, none of the button prompts being answered.
