遍历.csv文件时VBA运行时错误'1004' [英] VBA run-time error '1004' while looping through .csv file

问题描述

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

运行时"1004"错误

在线:

 设置searchInColumn = ActiveSheet.Cells(i，j).Offset(，-1).EntireColumn 

在此过程中，填充列中的数据将被放入第一列中的单元格中，并以分号分隔.

我的想法是在.csv和.xlsx文件上运行代码.代码被缩短.无论有问题的代码下方是什么代码，整个Sub都可以在.xlsx上运行，但是会卡在.csv上.当我摆脱所有FileDialog的内容，将代码放入.xlsb文件，将数据粘贴到其中(来自.csv)并从alt + f8列表运行宏时，它也运行良好.

我没有办法解决这个问题，您能给我一些提示吗?

  Sub FixCSV()昏暗的findMatch作为范围，searchInColumn作为范围昏暗的i长的，j长的，k长的，lastRow的长，lastColumn的长选择昏暗的文件为整数昏暗的选择文件为Office.FileDialogApplication.ScreenUpdating = False设置choiceFiles = Application.FileDialog(msoFileDialogFilePicker)使用chooseFiles.AllowMultiSelect =真.Title =请选择文件.".InitialFileName ="c:\".InitialView = msoFileDialogViewList结束于selectedFile = choiceFiles.Show如果selectedFile = -1然后对于k = 1要选择Files.SelectedItems.CountWorkbooks.Open ChooseFiles.SelectedItems(k)lastColumn = ActiveSheet.Cells(1，ActiveSheet.Columns.Count).End(xlToLeft).Columnj = 2直到j = lastColumn为止设置searchInColumn = ActiveSheet.Cells(1，j).Offset(，-1).EntireColumn设置findMatch = searchInColumn.Find(What:= ActiveSheet.Cells(1，j).Value)如果ActiveSheet.Cells(1，lastColumn).Offset(0，1).Value ="然后j = j + 1万一环形下一个k结束子 

解决方案

已解决问题，位于更正的代码下方:

 设置choiceFiles = Application.FileDialog(msoFileDialogFilePicker)使用chooseFiles.AllowMultiSelect =真.Title =请选择文件.".InitialFileName ="c:\".InitialView = msoFileDialogViewList.Filters.添加全部"，"*.*"结束于如果chooseFiles.Show = -1然后对于k = 1要选择Files.SelectedItems.Count'定义路径+文件名xlFileName = choiceFiles.SelectedItems(k)Workbooks.Open ChooseFiles.SelectedItems(k)以明确引用工作表以避免运行时'1004'错误:设置wrk = Workbooks.Open(xlFileName)设置Sh = wrk.Worksheets(1)'文件为.csv时的条件(根据以下条件将.csv数据分为几列:'分隔符csv):如果InStr(1，wrk.Name，".csv")然后Sh.Range(Range("A1")，Range("A1").End(xlDown)).TextToColumns _数据类型:= xlDelimited，_TextQualifier:= xlDoubleQuote，CondecutiveDelimiter:= False，Tab:= False，_分号:= True，逗号:= True，空格:= False，其他:= False万一lastRow = Sh.Cells(Sh.Rows.Count，"A").End(xlUp).RowlastColumn = Sh.Cells(1，Sh.Columns.Count).End(xlToLeft).Column我= 2直到i = lastRow为止做某事环形下一个k万一 

结束子

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

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

-在此处处理.csv: https://stackoverflow.com/a/8526046/10348607

I'm trying to loop through .csv file, opened through FileDialog. The code throws a

run-time '1004' error

on the line:

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

In the process, the data from populated columns is being put into cells in the first column, separated by semicolons.

My idea is to run the code on .csv and .xlsx files. Code is shortened. Whatever code is below the problematic line, the whole Sub works on .xlsx, but gets stuck on .csv. It also goes well when I get rid of all the FileDialog stuff, put the code in .xlsb file, paste the data there (from .csv) and run the macro from alt+f8 list.

I run out of ideas to solve this, could you give me some hint?

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

解决方案

Problem solved, below the corrected code:

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

End Sub

Answers that enabled me to solve the problem:

-on dealing with files opened with FileDialog: https://stackoverflow.com/a/21723463/10348607

-on dealing with .csv here: https://stackoverflow.com/a/8526046/10348607

这篇关于遍历.csv文件时VBA运行时错误'1004'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！