使用Access VBA将多个csv组合为单个xslx [英] Combine multiple csv to single xslx using Access VBA

问题描述

我无法尝试将不同的csv文件合并为单个xslx工作簿文件，作为MS Access中的单独工作表。我所查找的东西很多都是为了excel，但我需要能够在Access中使用这个。我发现对于excel vba我试图使用的形式的代码通常给我错误，大多数时候是对象的方法_Global'失败，即使我引用excel库，以及添加Excel。

I'm having trouble trying to combine different csv files into one single xslx workbook file as separate worksheets in MS Access. Alot of the things that I've looked up were for excel, but I need to be able to use this in Access instead. Code that I find for excel vba that I try to use in a form usually gives me errors, most of the time being "Method of object '_Global' failed", even when i reference the excel library, as well as add the Excel. prefix to all of the necessary objects such as workbook and worksheet.

编辑：

这是一个示例代码我尝试利用它从csv到xslx的转换

This is a sample code that I tried to utilise that does a converstion from csv to xslx

 Dim CSVfolder As String
 Dim XlsFolder As String
 Dim fname As String
 Dim wBook As Excel.Workbook

 CSVfolder = "C:\test\"
 XlsFolder = "C:\test\"

 fname = Dir(CSVfolder & "*.csv")

 Do While fname <> ""
 Set wBook = Excel.Workbooks.Open(CSVfolder & fname, Format:=6, Delimiter:=",")
 wBook.SaveAs XlsFolder & Replace(fname, ".csv", ""), FileFormatNum = 51
 wBook.Close False
 fname = Dir
 Loop

具体来说，错误在SaveAs行被捕获，这次对象'_Workbook'的方法'SaveAs'失败。

Specifically the error gets caught at the SaveAs line, this time saying Method 'SaveAs' of object '_Workbook' failed.

推荐答案

我想你想使用Access vba将多个csv文件合并到一个主Excel文件中？
如果是这样，首先在你的头上制定一个计划如何实现这一点。

I guess you want to merge multiple csv files into one master Excel file using Access vba? if so first make a plan in your head how to achieve this.

pseudo将是：

1. 在文件夹中搜索csv文件


2. 打开csv文件并将工作表/内容复制到主文件


3. 保存主文件


4. 执行上述步骤，直到没有更多文件可以执行

1. search for csv files in a folder
2. open the csv file and copy the sheet/content to the master file
3. save the master file
4. Do above steps until no more files left to do

在代码中是：

Private Sub Merge()
    'Create Excel application instance
    Dim xlApp As Object
    Set xlApp = CreateObject("Excel.Application")

    'Setup workbooks
    Dim wB As Excel.Workbook
    Dim wBM As Excel.Workbook

    'Csv files folder
    Dim CSVfolder As String
    CSVfolder = "C:\CsvFolder"

    'Master Excel file path
    Dim mF As String
    mF = Application.CurrentProject.path & "\Master.xlsx" 'Where your master file is

    'open the master file
    Set wBM = xlApp.Workbooks.Open(mF)

    'search and open the client files
    Dim fname As String
    fname = Dir(CSVfolder & "\*.csv")
    Do While fname <> ""
       'open the client file
       Set wB = xlApp.Workbooks.Open(CSVfolder & "\" & fname)
       'copy the first sheet from client file to master file
       wB.Sheets(1).Copy After:=wBM.Sheets(wBM.Sheets.count)
       'save master file
       wBM.Save
       'close client file
       wB.Close False
       'move to next client file
       fname = Dir()
    Loop

    xlApp.visible = True
    Set xlApp = Nothing
End Sub

请考虑在您的最终代码中捕获错误..
希望这有助于。

Please consider trapping errors in your final code.. hope this helps.

这篇关于使用Access VBA将多个csv组合为单个xslx的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！