Having issues working with multiple woorkbooks using vba

Question

I'm new at coding and have little experience in this field, and I'm trying to learn a few things using VB on Excel.

So, I was trying to catch in a XML a few words and write them on a new workbook, but I don't know why, when I call for the new workbook and try to edit it, it writes the values on the previous workbook, and that was not supposed to happen.

Sub loadXML()

Dim vFolder As String, vFile As String, vFileName As String
Dim vLineFile As Integer, vLineTAG As Integer, vLineResource As Integer
Dim vRange As String, vNode As String, vRange2 As String
Dim vWindowMain As String, vWindowFile As String, vWindowXML As String, vXMLSheetName As String
Dim XDoc As Object
Dim myNodes As IXMLDOMNodeList, myChildNodes As IXMLDOMNodeList
Dim myElement As IXMLDOMElement
Dim myNode As IXMLDOMNode, myChildNode As IXMLDOMNode
Dim nNode As Integer
Dim nChildNode As Integer
    
'principal
vWindowMain = ActiveWorkbook.Name

'planilha resource
Sheets("Plan1").Select
vRange = "A1"
vXMLSheetName = Range(vRange).Value + "Orionv5.xlsx"
Workbooks.Open vXMLSheetName, UpdateLinks:=False
vWindowXML = ActiveWorkbook.Name
vLineFile = 2
    
'main workbook
Windows(vWindowMain).Activate
Sheets("Plan1").Select
vRange = "A1"
vFolder = Range(vRange).Value
    
Do While True
    Application.ScreenUpdating = False
        
    vLineFile = vLineFile + 1
    Windows(vWindowMain).Activate
    Sheets("Plan1").Select
    vRange = "A" + Trim(Str(vLineFile))
    vFile = Range(vRange).Value
    vFileName = vFolder + "\" + vFile 'xml arquives are listed on the main workbook
        
    If vFile = "" Then
        Exit Do
    End If
        
    Windows(vWindowXML).Activate
    
    'open xml
    Set XDoc = CreateObject("MSXML2.DOMDocument")
    XDoc.async = False: XDoc.validateOnParse = False
    XDoc.load (vFileName)
    Set myNodes = XDoc.SelectNodes("//data/value")
    If myNodes.Length > 0 Then
        For nNode = 0 To myNodes.Length - 1
            Set myNode = myNodes(nNode)
            If myNode Is Nothing Then
                Debug.Print "Nenhum item encontrado"
            Else
                Set myChildNodes = myNode.ChildNodes ' Get the children of the first node.
                For nChildNode = 0 To myChildNodes.Length - 1
                    vNode = myChildNodes(nChildNode).Text
                    Windows(vWindowXML).Activate
                    With Workbooks("XmlOrionv5.xlsx") 'secondary workbook is not being the one receiving the values, why?
                        vRange2 = "A" + Trim(Str(nNode + 2))
                        Range(vRange2).Value = vNode
                    End With
                Next nChildNode
            End If
        Next nNode
    Else
        Debug.Print "Nenhum item encontrado"
    End If

Loop

I know the are variables which I'm not using and I don't think that is the best way to approach this task, but it is the one I'm making some sense right now. I had no problem using a similar function to write in a secundary workbook, but I was taking values from another Excel workbook instead of a xml text arquive. The with command is now my second approach... Before I used just a select workbook and select sheet, was also not working. Is it happening cause I'm working with pointers and arquive library? How do I fix this?

See [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) for a comprehensive guide of how to avoid using `Select`. — BigBen, Jul 27 '20 at 13:12

Tim Stack · Accepted Answer · 2020-07-27T13:26:17.607

1

Do not use Select and Activate. Although intuitively they seem very useful functions, in practise they rarely are of any use.

Instead, use a Workbook and Worksheet object. You can use these objects to then make changes exactly where you want them.

e.g.

Dim wb As Workbook
Dim ws As Worksheet

Set wb = Workbooks("NAME") 'Change the name to the correct name
Set ws = wb.Worksheets(1) 'Change nr. to correct index, or use name

With ws
    'e.g.
    .Range("A1:A10").Value = "test"    
End With

edited Jul 27 '20 at 13:26

answered Jul 27 '20 at 12:46

Tim Stack

3,209
3
18
39

1

Thank you so much, it worked like a charm! Will apply this to all my worksheets now. – Arthur Paiva Jul 27 '20 at 13:08
Glad I could help! – Tim Stack Jul 27 '20 at 13:29
When I don't write the dot right before range, it goes back to pasting the values on the wrong workbook. Do you know why the dot is required, or where can I learn why this happens? – Arthur Paiva Jul 27 '20 at 13:43
1

The `With` statement saves you from specifying the workbook/-sheet for every range. Usually, you would write `Workbooks("Name").Sheets(1).Range("A1").Value = 1` (or `ws.Range("A1").Value = 1` since `ws` here specifies a specific worksheet), but the `With` statement brings this down to `.Range("A1").Value = 1`. Leaving out the period would just make the `Range` refer to whatever workbook/-sheet is active. – Tim Stack Jul 27 '20 at 14:20

Having issues working with multiple woorkbooks using vba

1 Answers1