Running a macro against another workbook

[yellowc]

I have a workbook (Workbook A) that contains a macro that needs to run against another workbook (Workbook B) that's generated daily with the same name. Right now whenever I run what I have it just runs against Workbook A and I have to copy it over to Workbook B in order to get it to fire correctly. I tried workbooks(workbook B).activate. I also tried just opening both Workbook A and Workbook B, having Workbook B as the active window and running the macro. No good. Any thoughts?

Thanks

 

[JoeMo]

If you post the macro we can see if a simple adjustment can be made to have it act on the active workbook rather than the workbook that contains it.

 

[yellowc]

Sure, so I got a little further in the last few minutes. I created a button and I get an run-time 1004 error, macro may not be enabled or available. But I don't want the user to have to change Workbook B (the logisticare reffed by the button) too much to run it, Goal is to just open Workbook A and press button and have macro run on Workbook B

Public Sub getdirections()




Dim xmlhttp As New MSXML2.XMLHTTP60, myurl As String, c As Range, rng As Range, xmlresponse As New DOMDocument60
Dim myarray() As Variant, x As Long, d As Range, lastrow As Integer, rng2 As Range


'take cells and make it read like it's supposed to for the api stuff


Worksheets("Sheet1").Columns("F").Replace _
 What:=" ", Replacement:="+", _
 SearchOrder:=xlByColumns, MatchCase:=True
 
'finds the last row and assigns integer value
lastrow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row


'setting range length dynamically
rngstr = "f1:f" & lastrow
Set rng = Range(rngstr)


'this is for the array call later
rngstr2 = "g1:g" & lastrow
Set rng2 = Range(rngstr2)


'single substitution to get one location will work on looping in a bit
For Each c In rng
    myurl = "http://www.mapquestapi.com/directions/v2/route?key=key" & c & "&outFormat=xml&ambiguities=ignore&routeType=shortest"
    'Debug.Print myurl
    'sending the request
      xmlhttp.Open "GET", myurl, False
      xmlhttp.Send
      xmlresponse.LoadXML (xmlhttp.ResponseText)
      'once I got XML returns instead of JSON returns all started to work properly
     Set stats = xmlresponse.SelectNodes("//response/route/distance")
     'Sheets(1).Range("e2").Value = stats(0).Text
     ReDim Preserve myarray(x)
     myarray(x) = stats(0).Text
     x = x + 1
    'Range("d2 & rng").Value = stats(0).Text
Next




For x = LBound(myarray) To UBound(myarray)
    Debug.Print myarray(x)
 Next x




Private Sub CommandButton1_Click()
Application.Run "'C:\Users\location\Documents\CODE\Logisticare disputable trips by address22.xlsx '!getdirections"
End Sub

 

[yellowc]

I've also enabled macros on the target workbook just in case. Same error.

 

[Fluff]

You cannot have macros in an .xlsx file, should it be
address22.xlsm

 

[yellowc]

I actually don't think what I want to do is going to work properly. I want to be able to have the macro only in Workbook A but then open Workbook B and run the macro against workbook B. There would be no macro in Workbook B, just A would take Workbooks B data and use that in it's own macro. Is that a possibility?
Thanks for the help

 

[JoeMo]

Yes. that can work if you have B open and have your subroutine make B the active workbook then write the routine so all its actions are performed on the Activeworkbook, or you could set workbook objects at the start of the routine like:

Dim A as workbook, B as Workbook
Set A = Thisworkbook
Set B = Workbooks("Your B Book file  name and extension here")
'All actions to be done on B would be within the next With - End With construct
With B
'code to things to B
End with

You may also need to reference or set objects for specific worksheet(s) in B

 

[yellowc]

OK, different errror now did a test like below, but I get now object variable or with block variable not set

Public Sub testing()




Dim a As Workbook
Dim b As Workbook
Dim rng As Range
Dim rngstr As Range
Dim lastrow As Integer
Dim c As Range


Set a = ThisWorkbook
Set b = Workbooks("Logisticare disputable trips by address22.xlsx")




With b


lastrow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Debug.Print lastrow


rngstr = "G1:G" & lastrow


Set rng = Range(rngstr)


For Each c In rng
    c.Value = "100"
Next


End With


End Sub

 

[yellowc]

Nevermind about that, when I changed the G1:G to lowercase it started working but now with it all together it only runs through once and then gives me "object required"

Public Sub testing()




Dim a As Workbook
Dim b As Worksheet
Dim rngstr As Variant
Dim lastrow As Integer
Dim c As Range
Dim xmlhttp As New MSXML2.XMLHTTP60, myurl As String
Dim myarray() As Variant, d As Range, rng2 As Range


Set a = ThisWorkbook
Set b = Workbooks("Logisticare disputable trips by address22.xlsx").Worksheets("Sheet1")


With b
Dim x As Long
Dim rng As Range


lastrow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
'Debug.Print lastrow


rngstr = "f1:f" & lastrow
Set rng = Range(rngstr)


'this is for the array call later
rngstr2 = "g1:g" & lastrow
Set rng2 = Range(rngstr2)


'single substitution to get one location will work on looping in a bit
For Each c In rng
    myurl = "http://www.mapquestapi.com/directions/v2/route?key=c5OPvuJNmndQouh4KYx3vTISAG9GBsGX" & c & "&outFormat=xml&ambiguities=ignore&routeType=shortest"
    Debug.Print myurl
    'sending the request
      xmlhttp.Open "GET", myurl, False
      xmlhttp.Send
      xmlresponse.LoadXML (xmlhttp.ResponseText)
      'once I got XML returns instead of JSON returns all started to work properly
     Set stats = xmlresponse.SelectNodes("//response/route/distance")
     'Sheets(1).Range("e2").Value = stats(0).Text
     ReDim Preserve myarray(x)
     myarray(x) = stats(0).Text
     x = x + 1
Next




For x = LBound(myarray) To UBound(myarray)
    Debug.Print myarray(x)
 Next x
 
rng2.Value = Application.WorksheetFunction.Transpose(myarray)
End With


End Sub

 

[JLGWhiz]

See JoeMo's note at the bottom of post #7 . You need to get it down to the sheet level to reference ranges. This is a common mistake made because people are used to the 'Windows('myWorkbook').Activate method which automatically throws the active sheet up. In VBA You need to identify the Workbook and worksheet. You can do that several ways. I prefer to first Set the workbook, then Set the worksheet, qualified with the worbook variable, then use the sheet variable which will include the workbook. Here is a sample.

Dim wb As Workbook, sh As Worksheet
Set wb = Workbooks(1)
Set sh = wb.Sheets(1)

Now the sh variable can be used like

sh.Range("A1") = sh.Range("D4").Value

and sh means Workbooks(1).Sheets(1)