如何通过VBA将一个月的csv文件（命名日期）导入Excel？ [英] How can I import a month of csv files (date named) into Excel via VBA?

问题描述

我需要将一个月的CSV文件加载到Excel中，以便通过VBA进行分析。该月的每一天都是一个单独的文件，其日期名称（YYYYMMDD）。

I need to load a month of CSV files into Excel for analysis via VBA. Each day of the month is a separate file with the date name (YYYYMMDD).

当前，我可以加载使用两种不同情况创建的两个文件，A和B使用

Currently, I can load two files created by two different circumstances, A and B using

With ActiveSheet.QueryTables.Add(Connection:=Full_F_Name_A, _
                                 Destination:=Range("$H$4"))

我使用循环来更改A和B（以及目的地）。我还没有弄清楚如何增加日期。我使用输入框来获取当月第一个文件的日期。

I use a loop to change A and B (and the destination). I have not figured out how to increment the date. I use an input box to get the date of the first file in the month.

F_Name = InputBox("Enter name of first data file eg YYYYMMDD, target=H4, EG4")

任何帮助都会对我有所帮助...和初学者。

Any help would be great as I am stuck...and a beginner.

确定，请参见下面的VBA代码。收到的运行时错误 3001参数类型错误，超出可接受范围或彼此冲突。调试器指向 .cursorlocation = aduseclient行。也许我的电脑上缺少某些软件。 ADO网站上的介绍视频不再存在，因此我没有看到介绍。我将尝试另一种我知道的方式，即在我等待进一步的建议时，仅打开文件并将其转储到excel中。

OK OK, see VBA code below. Received Run-time error '3001' Arguments are of the wrong type, are out of acceptable range or are in conflict with one another. Debugger points to the ".cursorlocation = aduseclient" line. Perhaps there is some software missing on my PC. The intro video on the ADO website no longer exists so I did not see the intro. I will try the other way I know of just opening the files and dumping them into excel while I await further advice.

Sub Month_wdata_import()

Set cN = CreateObject("ADODB.Connection")
Set rS = CreateObject("ADODB.Recordset")

Dim sDate As String
Dim sDataPath As String
Dim i As Integer
Dim  mMax As Integer

sDataPath = Worksheets("D&L").Cells(1, "G").Value ' values located in 2nd sheet of workbook
mMax = Worksheets("D&L").Cells(1, "D").Value  'values located in 2nd sheet of workbook

For i = 1 To mMax
    sDate = "A_" + CStr(Worksheets("D&L").Cells(1 + i, "A").Value) ' looping through list of dates in sheet

    With cN
     .cursorlocation = aduseclient
     .CursorType = adopenstatic
     .LockType = adLockreadonly

       .Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
       "Data Source=" & sDataPath & ";" & _
       "Extended Properties=""text; HDR=Yes; FMT=Delimited; IMEX=1;""")
    End With

    With rS
      .ActiveConnection = cN
      .Source = "select * from data_" & sDate & "_.csv"
      .Open
    End With

Next

Range("A1").CopyFromRecordset rS

End Sub

推荐答案

好，是的，我正在为我自己的问题。...将为建议的方法获取程序。

OK yes I am providing an answer to my own question....will work on getting the program for the suggested method.

以下是有效的代码（仅重要位）：

Here is the code that is working (important bits only):

Dim all kinds of stuff

' Get the year and month of interest [User inputs the year and month they want the data analyzed YYYYMM

YY_MM = InputBox("Enter year and month of the daily cycles you want to analyze eg YYYYMM, no day, A_, B_ or _TC needed", target = D4, AI4)

'separate the YYYYMM date to YYYY and MM
F_year = Left(YY_MM, Len(YY_MM) - 2)
F_month = Right(YY_MM, Len(YY_MM) - 4)

'Determine # of days in the month 'The code below is from http://msdn.microsoft.com/en-us/library/aa227538(v=vs.60).aspx and it saved me from an If than nest from hell

mMax = DateSerial(CInt(F_year), (CInt(F_month) + 1), 1) - DateSerial(CInt(F_year), CInt(F_month), 1)

'The user must say where their data is by listing the path on the worksheet.  in this case it is a set template

sDataPath = Worksheets("Data").Cells(1, 4).Value ' value located in 1st sheet of workbook


    For i = 1 To mMax 'OK this is the file insertion For the A
set of data files

        If i < 10 Then   '  Need to add a zero to get the correct file name
            Z_singdig = "0" + CStr(i)
        Else
            Z_singdig = CStr(i)
        End If

        sDate = "A_" + YY_MM + Z_singdig + ".csv"   ' looping through list of dates ..

         'Label the data column with several file names so one can read it
        Label_F_Name = sDate + "........................."
        F_name = sDataPath + sDate

        With ActiveSheet.QueryTables.Add(Connection:="TEXT;" + F_name, Destination:=Range("D1048576").End(xlUp).Offset(1, 0)) 'insert the next file at the bottom of the first
            .Name = Label_F_Name
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertEntireRows
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = False
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileCommaDelimiter = True
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With

'report out the number of rows in each data file so I can use those as cell ref's later
        numofrows = Application.CountA(Range("D:D"))
        rownumout = numofrows - Corrtrow ' num of rows just added by the last file
        rowprtinc = 30 + i
        Numrowprt = "'data'!" + "DE" + CStr(rowprtinc)
        Range(Numrowprt) = rownumout ' entring the number of rows into a table on sheet 2
        Corrtrow = numofrows ' must track the current row num so the next row num can be corrected ...yeah that worked

    Next i

' do the same for the B set of data files..not shown here

End Sub

这篇关于如何通过VBA将一个月的csv文件（命名日期）导入Excel？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！