Avatar of kp
kp
 asked on

Copying xlsx hard-coded data to an excel sheet

I have got  an additional  requirement to save hard-coded data from excel sheet (Input.xlsx) (attached)   to a  excel sheet (in sheet 2 of output-23-10.xlsx) being  generated out of  below vbscript (Toxls.vbs) .

Looking to add  new functionality in  an existing utility vbscript (Toxls.vbs)  to achieve  copying hard-coded data from excel (Input.xlsx) to a  excel sheet (sheet2 of output-23-10.xlsx )   being generated from below vbscript  vbscript (Toxls.vbs) .  

Toxls.vbs :- Used to convert Pipe Delimited text into an excel sheet (Output.xlsx).


' Define constants
Const cExcel7 = 51
Const xlContinuous = 1
Const xlEdgeBottom = 9
Const xlEdgeLeft = 7
Const xlEdgeRight = 10
Const xlEdgeTop = 8

' Create file system object
Set objFSO = CreateObject("Scripting.FilesystemObject")

' Files to work woth
strInputFile = objFSO.GetAbsolutePathname("C:\Users\user\Desktop\Input.txt")
strOutputFile = objFSO.GetAbsolutePathname("C:\Users\user\Desktop\output-23-10.xlsx")

' Read text file into array
With objFSO.OpenTextFile(strInputFile, 1)
    arrInput = Split(.ReadAll, vbNewLine)
End With

' Start Excel, create a new worksheet
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.Workbooks.Add
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

' Initialize row index
intRow = 0

' Process each line of input file
For Each strInput in arrInput

    ' Skip all blank lines
    If strInput <> "" Then
        ' Start a new row in Excel, start at first column
        intRow = intRow + 1
        intCol = 0

        ' Parse input text line
        arrTokens = Split(strInput, "|")

        ' Add each value to Excel sheet
        For Each strToken In arrTokens
            intCol = intCol + 1
            With objSheet.Cells(intRow, intCol)
                .Value = Trim(strToken)

                ' Bold first row
                If intRow = 1 Then
                    .Font.Bold = True
                End If

                ' Borders on all cells
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeTop).LineStyle = xlContinuous
                .Borders(xlEdgeBottom).LineStyle = xlContinuous
                .Borders(xlEdgeRight).LineStyle = xlContinuous
           
            End With


    Next
   End If
Next

' Write file and close Excel
objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs strOutputFile, cExcel7
objExcel.ActiveWorkbook.Close False
objExcel.Quit


output-23-10.xlsx  (sheet1) and Toxls.vbs script  is attached for reference .
Input.xlsx
output-23-10.xlsx
Visual Basic.NETVB Script.NET ProgrammingMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Subodh Tiwari (Neeraj)

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Subodh Tiwari (Neeraj)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
kp

ASKER
Thanks a lot for your help ! Your solution  and timeliness is much appreciated.
Subodh Tiwari (Neeraj)

You're welcome!
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck