Results 1 to 2 of 2
  1. #1
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Exported as Text file to have a Header line

    Hi,
    The script exports the excel sheet as Text file successfuly, I want to add a Line on the top line of the text file as header ABCXYZ Company & todays date.

    Code:
    Sub processCells()
    
    zLastRow = Cells(Rows.Count, 1).End(xlUp).Row       'last data row
    
    For i = 1 To zLastRow                               'loop through all data rows
    zString = "'"                                                           'initialise string
    zString = zString & Right("000000" & Cells(i, 1), 6)                    'append col [A] data
    zString = zString & Right("000000000" & Cells(i, 2), 9)                 'append col [B] data
    zString = zString & Right("000" & Cells(i, 3), 3)                       'append col [C] data
    zString = zString & Right("0000000000000" & (Cells(i, 4) * 100), 13)    'append col [D] data
    zString = zString & Right("000000" & Cells(i, 5), 6)                    'append col [E] data
    Cells(i, 1) = zString                               'replace col [A] data with string
    Next i                                              'process next data row
    
    [b:e].Clear                                         'delete unwanted colums before file save
    
    zName = ThisWorkbook.Name                           'name of THIS workbook
    zSaveAs = ThisWorkbook.Path & "\" & zName & ".txt"  'append '.txt' for new text filename
    
    Application.DisplayAlerts = False                   'ignore any warnings (e.g. overwrite )
    ThisWorkbook.SaveAs zSaveAs, FileFormat:=xlTextMSDOS, CreateBackup:=False   'save as text file
    Application.DisplayAlerts = True                    'turn warnings back on just in case
    
    Workbooks.Open ThisWorkbook.Path & "\" & zName      'open orignal file
    Workbooks(zName).Activate                           'switch to original file
    
    zCommand = "C:\WINDOWS\notepad.exe " & zSaveAs      'create command to open text file in Notepad
    zReturnValue = Shell(zCommand, 1)                   'execute command
    
    ThisWorkbook.Close savechanges:=False               'close this amended version of original file
    
    End Sub

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Foncesa,

    This should do the trick.
    Code:
    Sub processCells()
    
    zLastRow = Cells(Rows.Count, 1).End(xlUp).Row       'last data row
    
    Cells(1,1) = "ABCXYZ Company " & Format(Now(),"mm/dd/yy")  '*** Header Line
    
    For i = 1 To zLastRow                               'loop through all data rows
    zString = "'"                                                           'initialise string
    zString = zString & Right("000000" & Cells(i, 1), 6)                    'append col [A] data
    zString = zString & Right("000000000" & Cells(i, 2), 9)                 'append col [B] data
    zString = zString & Right("000" & Cells(i, 3), 3)                       'append col [C] data
    zString = zString & Right("0000000000000" & (Cells(i, 4) * 100), 13)    'append col [D] data
    zString = zString & Right("000000" & Cells(i, 5), 6)                    'append col [E] data
    
    Cells(i+1, 1) = zString                             '*** replace col [A] data with string
    
    Next i                                              'process next data row
    
    [b:e].Clear                                         'delete unwanted colums before file save
    
    zName = ThisWorkbook.Name                           'name of THIS workbook
    zSaveAs = ThisWorkbook.Path & "\" & zName & ".txt"  'append '.txt' for new text filename
    
    Application.DisplayAlerts = False                   'ignore any warnings (e.g. overwrite )
    ThisWorkbook.SaveAs zSaveAs, FileFormat:=xlTextMSDOS, CreateBackup:=False   'save as text file
    Application.DisplayAlerts = True                    'turn warnings back on just in case
    
    Workbooks.Open ThisWorkbook.Path & "\" & zName      'open orignal file
    Workbooks(zName).Activate                           'switch to original file
    
    zCommand = "C:\WINDOWS\notepad.exe " & zSaveAs      'create command to open text file in Notepad
    zReturnValue = Shell(zCommand, 1)                   'execute command
    
    ThisWorkbook.Close savechanges:=False               'close this amended version of original file
    
    End Sub
    Note: Changed/Added lines have *** at the start of the comment ( 1 added line, 1 changed line)

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    foncesa (2013-11-25)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •