Iterate over whole Excel File

Question

I want to iterate over a whole excel file.

Sub Rechteck1_KlickenSieAuf()

Dim Zieldatei As String
Dim Line As Integer

'activate and protetct file
    ThisWorkbook.Worksheets(1).Activate
    ActiveWorkbook.Protect

    'Create desired file
    Zieldatei = Application.GetSaveAsFilename(FileFilter:="AVL (*.rtf), *.rtf", InitialFileName:="AVL.rtf")

    'Open desired file
    Open Zieldatei For Output As #1

     With ThisWorkbook.Worksheets(1)

      For Line = 1 To .Cells(.Rows.Count, 1).End(xlUp).Row



           'Write Read-In Data into target data
            Print #1, Join(Application.Transpose(Application.Transpose(.Range(.Cells(Line, 1), .Cells(Line, .Columns.Count).End(xlToLeft)).Value)), "|")


        Next
         End With

    Close #1

    Exit Sub

This code throws the message: "incompatible types". I don't know why. I would appreciate every help.

Answer 1

Try the following, I believe it should work, there's no need to activate the worksheet before protecting it, so I removed that line, changed the declaration from Integer to Long, and changed the Exit Sub to End Sub at the end:

Sub Rechteck1_KlickenSieAuf()
Dim Zieldatei As String
Dim Line As Long
'protect file
ThisWorkbook.Worksheets(1).Protect

'Create desired file
Zieldatei = Application.GetSaveAsFilename(FileFilter:="AVL (*.rtf), *.rtf", InitialFileName:="AVL.rtf")

'Open desired file
Open Zieldatei For Output As #1

    With ThisWorkbook.Worksheets(1)
        For Line = 1 To .Cells(.Rows.Count, 1).End(xlUp).Row
            'Write Read-In Data into target data
            Print #1, Join(Application.Transpose(Application.Transpose(.Range(.Cells(Line, 1), .Cells(Line, .Columns.Count).End(xlToLeft)).Value)), "|")
        Next
    End With

Close #1

End Sub

UPDATE:

Instead of using Or Error Resume Next, you could check to see how many columns in the given row, and check if only one column if it has any data in it, before going to the next line, like below:

Sub Rechteck1_KlickenSieAuf()
Dim Zieldatei As String
Dim Line As Long
Dim LineData As String
'protect file
ThisWorkbook.Worksheets(1).Protect

'Create desired file
Zieldatei = Application.GetSaveAsFilename(FileFilter:="AVL (*.rtf), *.rtf", InitialFileName:="AVL.rtf")

'Open desired file
Open Zieldatei For Output As #1

    With ThisWorkbook.Worksheets(1)
        For Line = 1 To .Cells(.Rows.Count, 1).End(xlUp).Row
            'Write Read-In Data into target data
            LastCol = .Cells(Line, .Columns.Count).End(xlToLeft).Column
            If Not LastCol = 1 And Not .Cells(Line, 1).Value = "" Then
                LineData = Join(Application.Transpose(Application.Transpose(.Range(.Cells(Line, 1), .Cells(Line, .Columns.Count).End(xlToLeft)).Value)), "|")
                Print #1, LineData
            End If

        Next
    End With

Close #1

End Sub