0

I have some code that will import a text file into a Worksheet with a partial match of the file name only. In this case the directory is C:\temp and the filename is dog_20220302.txt. The file will be imported into a new Workbook

The code is as follows

    Sub openfile()
    Dim fp As String, fn As String

    fp = "C:\temp\"
    fn = "dog*"

    fn = Dir(fp & fn & "*.txt")

    If CBool(Len(fn)) Then
        Workbooks.Open fp & fn, delimiter:=","
    End If
   
    End Sub

My question is how can I adjust the Workbooks.Open fp & fn, delimiter:="," part of the code so that the imported text file does NOT open a new workbook but imports the data into a specified worksheet in the active workbook ?

John
  • 321
  • 5
  • 16
  • 2
    For example with QueryTable: https://stackoverflow.com/questions/12197274/is-there-a-way-to-import-data-from-csv-to-active-excel-sheet – Pᴇʜ Sep 29 '22 at 14:41
  • 2
    As another option you could do `Set wb = Workbooks.Open fp & fn, delimiter:=","` so that you have the variable `wb` set to this new workbook, then copy the sheet to your existing workbook `wb.sheet1.Copy After:=ThisWorkbook.Sheets(wb.sheets.count)` Then close other workbook. `wb.close`. – JNevill Sep 29 '22 at 14:45
  • Used the following code thanks to your suggestion Workbooks.Open fp & fn, delimiter:="," ActiveWorkbook.ActiveSheet.Range("A1:A20").Copy Workbooks("Extraction.xlsm").Worksheets("sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues ' This will copy data to this WB i require (the macro is running from) ActiveWorkbook.Close ' Closes the temporary WB that i no longer need – John Sep 30 '22 at 12:04

0 Answers0