时间戳

首页 > 解决方案 > 循环通过 .csv 文件时出现 VBA 运行时错误“1004”

excel - 循环通过 .csv 文件时出现 VBA 运行时错误“1004”

问题描述

我正在尝试遍历通过 FileDialog 打开的 .csv 文件。代码抛出一个

运行时“1004”错误

在线上：

Set searchInColumn = ActiveSheet.Cells(i, j).Offset(, -1).EntireColumn

在这个过程中，来自填充列的数据被放入第一列的单元格中，用分号分隔。

我的想法是在 .csv 和 .xlsx 文件上运行代码。代码被缩短。无论代码在有问题的行下方，整个 Sub 都可以在 .xlsx 上运行，但会卡在 .csv 上。当我摆脱所有 FileDialog 的东西，将代码放入 .xlsb 文件中，将数据粘贴到那里（来自 .csv）并从 alt+f8 列表运行宏时，它也很顺利。

我没有解决这个问题的想法，你能给我一些提示吗？

Sub FixCSV()

    Dim findMatch As Range, searchInColumn As Range
    Dim i As Long, j As Long, k As Long, lastRow As Long, lastColumn As Long
    Dim chosenFile As Integer
    Dim chooseFiles As Office.FileDialog

    Application.ScreenUpdating = False

    Set chooseFiles = Application.FileDialog(msoFileDialogFilePicker)

        With chooseFiles      
            .AllowMultiSelect = True
            .Title = "Please select the file."
            .InitialFileName = "c:\"
            .InitialView = msoFileDialogViewList
        End With

     chosenFile = chooseFiles.Show

    If chosenFile = -1 Then
        For k = 1 To chooseFiles.SelectedItems.Count
            Workbooks.Open chooseFiles.SelectedItems(k)          

            lastColumn = ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column           

            j = 2

            Do Until j = lastColumn

                Set searchInColumn = ActiveSheet.Cells(1, j).Offset(, -1).EntireColumn
                Set findMatch = searchInColumn.Find(What:=ActiveSheet.Cells(1, j).Value)

                If ActiveSheet.Cells(1, lastColumn).Offset(0, 1).Value = "" Then                     
                        j = j + 1    
                End If
            Loop
        Next k
End Sub

标签： excelvba

解决方案

问题已解决，在更正的代码下方：

Set chooseFiles = Application.FileDialog(msoFileDialogFilePicker)

    With chooseFiles     
        .AllowMultiSelect = True
        .Title = "Please select the file."
        .InitialFileName = "c:\"
        .InitialView = msoFileDialogViewList
        .Filters.Add "All", "*.*"          
    End With

If chooseFiles.Show = -1 Then
    For k = 1 To chooseFiles.SelectedItems.Count

        'defining path+file name
        xlFileName = chooseFiles.SelectedItems(k)
        Workbooks.Open chooseFiles.SelectedItems(k)

        'to reference Worksheets explicitly in order to avoid the run-time 
         '1004' error:
        Set wrk = Workbooks.Open(xlFileName)
        Set Sh = wrk.Worksheets(1)

        'condition in case a file is .csv (splits .csv data into columns according to 
          'separators csv):       
        If InStr(1, wrk.Name, ".csv") Then              
            Sh.Range(Range("A1"), Range("A1").End(xlDown)).TextToColumns _
            DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
            Semicolon:=True, Comma:=True, Space:=False, Other:=False               
        End If

        lastRow = Sh.Cells(Sh.Rows.Count, "A").End(xlUp).Row
        lastColumn = Sh.Cells(1, Sh.Columns.Count).End(xlToLeft).Column

        i = 2           
        Do Until i = lastRow
           'do sth
        Loop
    Next k
End If

结束子

使我能够解决问题的答案：

-on 处理使用 FileDialog 打开的文件：https ://stackoverflow.com/a/21723463/10348607

-在这里处理.csv：https ://stackoverflow.com/a/8526046/10348607

推荐阅读