Modify the output file

lalaww123

New Member
Joined
Dec 11, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
1607713258458.png

I would like to add the highlighted things to the output. please see pic above.


VBA Code:
Sub GetOutput()
    Dim LastRowIndex As Integer
    Dim RowIndex As Integer
    Dim UsedRng As Range
    Dim ws As Worksheet
    Dim Ot As Worksheet
    Dim str As String
   
    Dim rowL As Integer
    Dim rowLast As Integer
    Set ws = ActiveWorkbook.Sheets("Input")
    Set Ot = ActiveWorkbook.Sheets("Output")

   
    rowL = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If rowL > 2 Then
        Ot.UsedRange.EntireRow.Delete
        Ot.Cells(1, 1) = ""
        Ot.Cells(1, 6) = ""
        Ot.Cells(1, 3) = ""
        Ot.Cells(1, 5) = ""
        Ot.Cells(1, 2) = ""
        Ot.Cells(1, 4) = ""
        Ot.Cells(1, 7) = ""
       
       
        For i = 3 To rowL
            If ws.Cells(i, 2) = "CK:" Then
                rowLast = Ot.Cells(Ot.Rows.Count, "A").End(xlUp).Row + 1
                Ot.Cells(rowLast, 1) = 3245543
                Ot.Cells(rowLast, 6) = Null
                Ot.Cells(rowLast, 7) = Null
                Ot.Cells(rowLast, 3) = ws.Cells(i, 3)
                Ot.Cells(rowLast, 5) = ws.Cells(i, 4)
                Ot.Cells(rowLast, 2) = CDate(ws.Cells(i, 1))
                'Ot.Cells(rowLast, 6) = Format(CDbl(ws.Cells(i + 1, 6)), "$ #,##0.00")
                Ot.Cells(rowLast, 4) = Format(CDbl(ws.Cells(i + 2, 6)), "$ #,##0.00")
            End If
   
        Next i
    End If
'Ot.Activate
'Call DoTheExport
End Sub
Public Sub ExportToTextFile(FName As String, _
    Sep As String, SelectionOnly As Boolean, _
    AppendData As Boolean)

Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim CellValue As String


Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile
ActiveWorkbook.Worksheets("OutPut").Activate
If SelectionOnly = True Then
    With Selection
        StartRow = .Cells(1).Row
        StartCol = .Cells(1).Column
        EndRow = .Cells(.Cells.Count).Row
        EndCol = .Cells(.Cells.Count).Column
    End With
Else
    With ActiveSheet.UsedRange
        StartRow = .Cells(1).Row
        StartCol = .Cells(1).Column
        EndRow = .Cells(.Cells.Count).Row
        EndCol = .Cells(.Cells.Count).Column
    End With
End If

If AppendData = True Then
    Open FName For Append Access Write As #FNum
Else
    Open FName For Output Access Write As #FNum
End If

For RowNdx = StartRow To EndRow
    WholeLine = ""
    For ColNdx = StartCol To EndCol
        If Cells(RowNdx, ColNdx).Value = "" Then
            CellValue = Chr(34) & Chr(34)
        Else
           CellValue = Cells(RowNdx, ColNdx)
        End If
        WholeLine = WholeLine & CellValue & Sep
    Next ColNdx
    WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
    Print #FNum, WholeLine
   
Next RowNdx

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum
ActiveWorkbook.Worksheets("Main").Activate
End Sub
Sub DoTheExport()
Dim str As String
str = ActiveWorkbook.Path & "\Check Register " + Format(Now(), "DDMMMYYYY") _
& ".txt"
    ExportToTextFile FName:=str, Sep:=",", _
       SelectionOnly:=False, AppendData:=False
ActiveWorkbook.FollowHyperlink str, NewWindow:=True

End Sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Watch MrExcel Video

Forum statistics

Threads
1,127,326
Messages
5,624,018
Members
416,006
Latest member
PCaffrey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top