Copy data from .txt file to an Excel worksheet in a workbook that is not open

[billfinn]

I need to copy all of the data in a text file and then paste it into a specific worksheet in an excel workbook that is not open. Most of the info I have found is for the active worksheet, not what I am looking for.
Been working on this longer than I should have. It keeps quitting when it gets to the next line. Probably a syntax issue. The text file is just a dump from the AS400, I need to pull it into the specific worksheet 13 and then I'll continue on in formatting the sheet

Sub DATA_IMPORT()
Dim oFso: Set oFso = CreateObject("Scripting.FileSystemObject")
Dim oFile: Set oFile = oFspenTextFile("H:\Quick Estimator Project\QPQUPRFIL_GJH_QDFTJOBD_907659_1.txt", 1)
Dim sText
sText = oFile.ReadAll
oFile.Close
Workbooks.Open ("H:\Quick Estimator Project\Estimating Tool 06-06-2018.xlsm")
Workbook.Sheets(13).Range("A:J:").Value = sText

End Sub

thanks,
Bill

 

[igold]

Hi Bill,

Welcome to the MrExcel board.

See if this does what you want. Please note that I added a row number to your paste or else you will fill the entire columns ("A:J") with your text...

Sub DATA_IMPORT()


    Dim oFso: Set oFso = CreateObject("Scripting.FileSystemObject")
    Dim oFile: Set oFile = oFso.OpenTextFile("H:\Quick Estimator Project\QPQUPRFIL_GJH_QDFTJOBD_907659_1.txt", 1)
    Dim sText
    
    sText = oFile.ReadAll
    oFile.Close
    Workbooks.Open ("H:\Quick Estimator Project\Estimating Tool 06-06-2018.xlsm")
    Workbooks("Estimating Tool 06-06-2018.xlsm").Sheets(13).Range("A6:J6").Value = sText
    
End Sub