title: Extracting Excel data into pipe delimited file - How to refer to other workbook
body: <p>I am working on creating excel file, that includes only button that, when clicked on, extracts all other excel files in specified (now just hardcoded) directory into pipe delimited .txt files. I did some programming in the past but I am not familiar much with VBA, so I usually work by searching and reusing tutorials and such.</p>
  
  <p>Basicaly what I want to do is:
  - Loop through specific directory
  - For each xls/xlsx file, create separate .txt pipe delimited extract of the same name with .txt extension</p>
  
  <p>So far, I got to this code:</p>
  
  <pre><code>    Sub Run_Coversion()
  
  Dim directory As String
  Dim fileName As String
  Dim OutputFile As String
  
  Dim myWkBook As Workbook
  Dim myRecord As Range
  Dim myField As Range
  Dim nFileNum As Long
  Dim sOut As String
  Const DELIMITER As String = "|"
  
  Application.ScreenUpdating = False
  directory = "C:\Users\vacek\Documents\EFPIA_Project\Excel_Tool\Files\"
  fileName = Dir(directory &amp; "*.xls*")
  Do While fileName &lt;&gt; ""
  Workbooks.Open (directory &amp; fileName)
  OutputFile = directory &amp; fileName &amp; ".txt"
  nFileNum = FreeFile
  Open OutputFile For Output As #1
  Set myWkBook = Workbooks(fileName)
  myWkBook.Sheets("Sheet1").Activate
  
  For Each myRecord In Range("A1:A" &amp; Range("A" &amp; Rows.Count).End(xlUp).Row)
      With myRecord
          For Each myField In Range(.Cells, Cells(.Row, Columns.Count).End(xlToLeft))
          sOut = sOut &amp; DELIMITER &amp; myField.Text
          Next myField
          Print #nFileNum, Mid(sOut, 2)
          sOut = Empty
      End With
  Next myRecord
  End With
  
  Workbooks(fileName).Close
  fileName = Dir()
  Loop
  Application.ScreenUpdating = True
  End Sub
  </code></pre>
  
  <p>The problem is, that the "For each" loop goes through workbook that is running the macro, not the one, that is opened. I experimented with setting active of that workbook, but I cannot make it working. Can anyone help me to set this right?</p>