遍历整个Excel文件 [英] Iterate over whole Excel File

问题描述

我要遍历整个Excel文件.

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.

推荐答案

尝试以下操作，我认为它应该可以工作，在保护它之前无需激活工作表，因此我删除了该行，将声明从Integer更改为Long，最后将Exit Sub更改为End Sub:

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

更新:

您可以检查给定行中有多少列，而不是使用Or Error Resume Next，然后在转到下一行之前检查是否只有一个列中包含任何数据，如下所示:

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

这篇关于遍历整个Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！