Have a VB script that is not working. RRS feed

  • Question

  • I have a script that is supposed to copy and paste from a tabbed txt file to an excel spreadsheet. It is not pasting for some reason. What the heck am I missing??

    It should be pasting the last 8 lines of the tabbed file into excel @ A, 3.

    Const ForReading = 1 Set objFSO = CreateObject("Scripting.FileSystemObject") ' get number of lines Set objTextFile = objFSO.OpenTextFile("c:\test.txt", ForReading) objTextFile.ReadAll lineCount = objTextFile.Line objTextFile.Close Set objTextFile2 = objFSO.OpenTextFile("c:\test.txt", ForReading) ' skip all lines except 8 last i = 0 while i < lineCount-8 objTextFile2.ReadLine i = i+1 wend ' prepare excel Set objExcel = CreateObject("Excel.Application") Set objWorkbook = objExcel.Workbooks.Open("c:\NBP ESP-152 REV F TEMPLATE.xlsx") objExcel.Application.Visible = True j = 1 ' read last 8 lines Do Until objTextFile2.AtEndOfStream ' read line line = objTextFile2.ReadLine ' write to console ' WScript.Echo line (for testing) ' and into workbook objExcel.Cells(1, j).Value = strLine j=j+1 Loop

    Thanks in advance!

    • Moved by Bill_Stewart Tuesday, December 5, 2017 3:44 PM This is not "fix/debug/rewrite my script for me" forum
    Monday, November 6, 2017 7:33 PM

All replies

  • You have to save the file to see the changes.  Also you cannot paste a string into more than one cell at a time.

    Look in the Gallery for examples of how to script Excel.  I also recommend using PowerShell and not VBScript.


    Monday, November 6, 2017 7:38 PM