none
Need to add a column in front in my current Vb script RRS feed

  • General discussion

  •  Attached is the script that I use for copying text from multiple files and transfering them to the excell sheet, listed below is my current outout in excell. Need a column in front for dates for all the rows. The date is also contained in the text file 4 th row. Listed below also is the text file that i read from. Any help is very much appriciated. Thanks

    

    'Option Explicit Sub OpenMultipleFiles() Application.ScreenUpdating = False Dim fn As Variant, f, c As Integer c = 1 fn = Application.GetOpenFilename("All_files,*.*", _ 1, "Select One Or More Files To Open", , True) If TypeName(fn) = "Boolean" Then Exit Sub For f = 1 To UBound(fn) Debug.Print "Selected file #" & f & ": " & fn(f) 'Workbooks.Open fn(f) Open fn(f) For Input As #f Ctr = 0 Do Line Input #f, Data Ctr = Ctr + 1 Cells(Ctr, 1).Value = Data Loop While EOF(f) = False Close #f Cells(1, 1).Resize(Ctr, 1).TextToColumns Destination:=Range("A1"), _ DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, Comma:=False _ , Space:=False, Other:=True, OtherChar:="=", FieldInfo:=Array(1, 1), _ TrailingMinusNumbers:=True 'Create new sheet with raw data 'Mycell1 = Sheets("Sheet2").Cells(1, c) 'Find last cell with data 'On Error Resume Next Worksheets("Sheet1").Range("A11:A30").Copy Destination:=Worksheets("Sheet2").Cells(c, 1) c = c + 21 Sheets("Sheet1").Select Cells.ClearContents 'Clear data on sheet1 Range("A1").Select Next f Application.ScreenUpdating = True End Sub

    Text File date is read from

    LOT SUMMARY [24 HOURS] ---------------------- Machine ID IPIe 29 Start Date 12-18-2013 Start Time 00:00:00 End Time 00:00:00 ------------------------------------------------------------------------------------------------------ Inspection Summary TOTAL TRACK 1 TRACK 2 TRACK 3 TRACK 4 TRACK 5 ------------------------------------------------------------------------------------------------------ Units Inspected 195 35 40 40 40 40 Units Passed 0 0 0 0 0 0 Units Yield 0.00 0.0 0.0 0.0 0.0 0.0 Units Failed 195 35 40 40 40 40 Grab Retry 0 0 0 0 0 0 Saline Shortage 0 0 0 0 0 0 Saline In-Range 0 0 0 0 0 0 Saline Excess 0 0 0 0 0 0 Defects ------- Spot 0 0 0 0 0 0 Non-Circular 0 0 0 0 0 0 Tear Lens 0 0 0 0 0 0 Gap Lens 0 0 0 0 0 0 No Lens 0 0 0 0 0 0 No Saline 195 35 40 40 40 40 Inner Tear 0 0 0 0 0 0 ------------------------------------------------------------------------------------------------------



    Current Output, need one column in the left for dates for all the rows.

    Units Inspected 195 35 40 40 40 40 Units Passed 0 0 0 0 0 0 Units Yield 0.00 0.0 0.0 0.0 0.0 0.0 Units Failed 195 35 40 40 40 40 Grab Retry 0 0 0 0 0 0 Saline Shortage 0 0 0 0 0 0 Saline In-Range 0 0 0 0 0 0 Saline Excess 0 0 0 0 0 0 Defects ------- Spot 0 0 0 0 0 0 Non-Circular 0 0 0 0 0 0 Tear Lens 0 0 0 0 0 0 Gap Lens 0 0 0 0 0 0 No Lens 0 0 0 0 0 0 No Saline 195 35 40 40 40 40 Inner Tear 0 0 0 0 0 0



    • Edited by Morgan Naidu Wednesday, October 1, 2014 2:39 PM
    • Moved by Just Karl Wednesday, October 1, 2014 7:27 PM OP Requested
    • Moved by Bill_Stewart Wednesday, October 1, 2014 7:35 PM Move to more appropriate forum
    Wednesday, October 1, 2014 2:37 PM

All replies

  • Hello,

    I'd ask in The Official Scripting Guys Forum!

    Karl


    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer.
    My Blog: Unlock PowerShell
    My Book: Windows PowerShell 2.0 Bible
    My E-mail: -join ('6F6C646B61726C406F75746C6F6F6B2E636F6D'-split'(?<=\G.{2})'|%{if($_){[char][int]"0x$_"}})

    Wednesday, October 1, 2014 5:58 PM
  • Karl,

    This is my first time posting. Are you requesting that I should post this in the Offical Scripting Guys Forum.

    Thanks

    Wednesday, October 1, 2014 6:58 PM
  • Hello,

    I can move the question, if you'd like.

    Karl


    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer.
    My Blog: Unlock PowerShell
    My Book: Windows PowerShell 2.0 Bible
    My E-mail: -join ('6F6C646B61726C406F75746C6F6F6B2E636F6D'-split'(?<=\G.{2})'|%{if($_){[char][int]"0x$_"}})

    Wednesday, October 1, 2014 7:03 PM
  • Karl, Plese move it, thank for your help.

    Morgan

    Wednesday, October 1, 2014 7:07 PM
  • This is VBA code and not script. It belongs in the VBA Office developers forum.


    ¯\_(ツ)_/¯

    Wednesday, October 1, 2014 7:50 PM
  • To clarify - this line:

       Debug.Print "Selected file #" & f & ": " & fn(f)

    is a VBA line and not avaialbel in script.  The whole script is not structured for VBAScritp and cannot be used in VBScript without changes.


    ¯\_(ツ)_/¯

    Wednesday, October 1, 2014 7:51 PM
  • YOU appear to be copying a range to a target sheet.  Just move the target over one column and backfill with dates.  That should be even easier then the rest of the VBA code was.


    ¯\_(ツ)_/¯

    Wednesday, October 1, 2014 7:53 PM