tag:blogger.com,1999:blog-28492262.post8764371976260683750..comments2023-12-27T22:22:25.151-08:00Comments on VBA Tips & Tricks: Run a Macro from Different WorkbookUnknownnoreply@blogger.comBlogger10125tag:blogger.com,1999:blog-28492262.post-1823394813110797982013-07-31T03:01:43.211-07:002013-07-31T03:01:43.211-07:00Hi,
Use a variable to get the current file name a...Hi,<br /><br />Use a variable to get the current file name and use that variable in the syntax above instead giving a file name in the quotes. For e.g<br />a=Application.ActiveWorkbook.Name<br />Workbooks(a).Worksheets("data").Range("a1:g65536").ClearContentsSachin Sharmanoreply@blogger.comtag:blogger.com,1999:blog-28492262.post-22607462536031754352012-07-27T00:27:10.465-07:002012-07-27T00:27:10.465-07:00The macro that I am using uses the name of the wor...The macro that I am using uses the name of the workbook, as a result whenever i have to rename the excel workbook i have to edit the code accordingly.<br />For instance:<br />My excel file name is test i uses the code<br /> Workbooks("test.xls").Worksheets("data").Range("a1:g65536").ClearContents<br /><br />and if i have to rename the excel file as test2 i have to change the code as<br /> Workbooks("test2.xls").Worksheets("data").Range("a1:g65536").ClearContents<br /><br />Can you please help .I am new when it comes to using macros.I am using excel 2007<br />Thanks in advanceAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-28492262.post-39462231862882911902012-02-25T02:01:17.731-08:002012-02-25T02:01:17.731-08:00Note that if you use variables as filename and it ...Note that if you use variables as filename and it contains special characters like spaces, use <br /><br />Application.Run "'" & sYourFilename &"'!AnotherWrkBook_Macro"<br /><br />This took me a while to grasp..Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-28492262.post-84478673933222269962011-03-23T02:46:19.878-07:002011-03-23T02:46:19.878-07:00I am trying to run a macro on one Excel workBook, ...I am trying to run a macro on one Excel workBook, The macro is located in another excel workbook. When iam trying to run the macro using syntax ("C:\Test_Macro.xls!Hello_World") then i got COM exception. for clarification here is my code of what iam doing. Thanks for your help in advance<br /><br /> Dim oExcel As Excel.ApplicationClass<br /> Dim oBook As Excel.WorkbookClass<br /> Dim oBooks As Excel.Workbooks<br /> <br /> 'Start Excel and open the workbook.<br /> oExcel = New Excel.Application<br /> oExcel.Visible = False<br /> oBooks = oExcel.Workbooks<br /> ' workbook in which i want to run marco<br /> oBook = oBooks.Open("c:\Hello_Macro.xls")<br /><br /> 'Run the macros.<br /> oExcel.Run("C:\Test_macro.xls!TEST2")<br /> oBook.Save()<br /><br /> oBook.Close(False)<br /> System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook)<br /> oBook = Nothing<br /> System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks)<br /> oBooks = Nothing<br /> oExcel.Quit()<br /> System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)<br /> oExcel = Nothing<br /><br />regards<br />TalhaAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-28492262.post-48667312300627342152011-03-23T02:44:16.127-07:002011-03-23T02:44:16.127-07:00I am trying to run a macro on one Excel workBook, ...I am trying to run a macro on one Excel workBook, The macro is located in another excel workbook. When iam trying to run the macro using syntax ("C:\Test_Macro.xls!Hello_World") then i got COM exception. for clarification here is my code of what iam doing. Thanks for your help in advance<br /><br /> Dim oExcel As Excel.ApplicationClass<br /> Dim oBook As Excel.WorkbookClass<br /> Dim oBooks As Excel.Workbooks<br /> <br /> 'Start Excel and open the workbook.<br /> oExcel = New Excel.Application<br /> oExcel.Visible = False<br /> oBooks = oExcel.Workbooks<br /> ' workbook in which i want to run marco<br /> oBook = oBooks.Open("c:\Hello_Macro.xls")<br /><br /> 'Run the macros.<br /> oExcel.Run("C:\Test_macro.xls!TEST2")<br /> oBook.Save()<br /><br /> oBook.Close(False)<br /> System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook)<br /> oBook = Nothing<br /> System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks)<br /> oBooks = Nothing<br /> oExcel.Quit()<br /> System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)<br /> oExcel = Nothing<br /><br />regards<br />TalhaAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-28492262.post-31043397802573006262009-08-12T18:57:23.173-07:002009-08-12T18:57:23.173-07:00Here is a hint:
Sub Find_A_Cell()
Dim oWS As Wo...Here is a hint:<br /><br /><i><br />Sub Find_A_Cell()<br /><br />Dim oWS As Worksheet<br />Dim oRng As Range<br />Dim FirstUL<br /><br />Set oWS = ActiveSheet<br /><br />Application.FindFormat.Clear<br />Application.FindFormat.Font.Color = vbBlue<br /><br /><br />Set oRng = oWS.Range("C:C").Find(What:="YES", LookIn:=xlValues, LookAt:= _<br /> xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)<br />If Not oRng Is Nothing Then<br /> FirstUL = oRng.Address<br /> Do<br /> oRng.Font.Color = vbRed<br /> Set oRng = oWS.Range("C:C").FindNext(oRng)<br /> Loop While Not oRng Is Nothing And oRng.Address <> FirstUL<br />End If<br />End Sub<br /><br /><br /><br /></i>M Shasurhttps://www.blogger.com/profile/17390752937266096344noreply@blogger.comtag:blogger.com,1999:blog-28492262.post-47415305017361145522009-08-12T00:08:31.578-07:002009-08-12T00:08:31.578-07:00I am working in a workbook called Invoice, Locatio...I am working in a workbook called Invoice, Location of the Master Workbook is specified in a cell of Invoice Workbook. I need a macro to search a word "YES" in column C:C in the Master Workbook.Unknownhttps://www.blogger.com/profile/04832947960353504180noreply@blogger.comtag:blogger.com,1999:blog-28492262.post-3583385318749843472009-03-12T07:28:00.000-07:002009-03-12T07:28:00.000-07:00I ended up doing the following: Hopefully, this wi...I ended up doing the following: Hopefully, this will help someone else:<BR/><BR/>Private Sub CommandButton1_Click()<BR/> Dim controlws As Worksheet<BR/> Set controlws = Application.ActiveSheet<BR/> col = 6<BR/> Row = 11<BR/> <BR/> For i = 1 To controlws.Cells(Row, col).Value<BR/> Row = Row + 1<BR/> bookname = controlws.Cells(Row, col).Value<BR/> <BR/> Application.StatusBar = "Opening workbook " & bookname<BR/> Set wb = Workbooks.Open(Filename:=bookname)<BR/> <BR/> Application.StatusBar = "Getting data for workbook " & bookname<BR/> <BR/> ' For this to work -> and un-privatize this macro in the spreadsheet<BR/> Call wb.Sheets.Item("FOUNDRY_SETUP").CommandButton1_Click<BR/> Next i<BR/> <BR/> Application.StatusBar = "Done!"<BR/>End SubAjay Gautamhttps://www.blogger.com/profile/02216624495352815059noreply@blogger.comtag:blogger.com,1999:blog-28492262.post-41554030216050057372009-03-11T08:48:00.000-07:002009-03-11T08:48:00.000-07:00IF you want to execute a macro in a specific modul...IF you want to execute a macro in a specific module you can try<BR/><BR/>Application.Run "'C:\CanBeDeleted.xlsx.xlsm'!Sheet1.AnotherWrkBook_Macro"M Shasurhttps://www.blogger.com/profile/17390752937266096344noreply@blogger.comtag:blogger.com,1999:blog-28492262.post-90963725134723330902009-03-09T14:28:00.000-07:002009-03-09T14:28:00.000-07:00Thanks. Nice entry.My situation is a bit different...Thanks. Nice entry.<BR/><BR/>My situation is a bit different. The macro I want to run in in a specific worksheet.<BR/><BR/>Any idea how would I identify the worksheet in Application.Run "'C:\CanBeDeleted.xlsx.xlsm'!AnotherWrkBook_Macro"<BR/><BR/>ThanksAjay Gautamhttps://www.blogger.com/profile/02216624495352815059noreply@blogger.com