如何转换Excel文件，然后一步导入到Access？ [英] How do I convert Excel files, then import to Access in one step?

问题描述

我昨天发布了这个问题，但是人们建议我再问一次，更多的细节。

我有几个（+1000）原始文件，我需要转换，最终导入Access

。我写了一个Excel宏，将这样做。这是相当广泛的，有很多的操作，但它的要点在下面。它要求用户选择一些文件，然后将文件保存到与原始原始文件相同的目录。它被称为ConvertWithDialog，为了节省空间，在下面的粘贴中。

 < script src =https://pastebin.com/embed_js/2cfKLbAr>< / script>  

我还有一个没有对话框的版本，可以通过单独的功能将文件传递给它来运行。

 < script src =https://pastebin.com/embed_js/9AB8VKgL>< / script>  

我的问题正在导入到Access。如何转换多个文件并导入到Access一步（Access中的一个按钮）？我不想要运行convert函数，等待文件完成转换，然后导入到Access。我试过这个，如用户之前，但是当我添加参数时，VBA会发出一个错误，表示'='。我有一个宏来批量导入Access文件，并且可以包含它。但是它不是很优雅，我相信我最终还是要做不同的做法。

解决方案

通过TransferSpreadsheet（VBA）从单个文件夹中的所有EXCEL文件导入数据

从位于单个文件夹中的所有EXCEL文件中的第一个（或唯一）工作表中导入数据的通用代码。所有EXCEL文件的工作表必须具有相同的布局和格式的数据。

  Dim strPathFile As String，strFile As String ，strPath As String 
 Dim strTable As String 
 Dim blnHasFieldNames As Boolean 
 
'如果EXCEL工作表中的第一行
'具有字段名称，请将此下一行更改为True 
 blnHasFieldNames = False 
 
'用
'包含EXCEL文件的文件夹的真实路径替换C：\Documents\ 
 strPath =C： \Documents\
 
'将tablename替换为要导入数据的
'的表的真实名称
 strTable =tablename
 
 strFile = Dir（strPath&* .xls）
 Do While Len（strFile）> 0 
 strPathFile = strPath& strFile 
 DoCmd.TransferSpreadsheet acImport，acSpreadsheetTypeExcel9，_ 
 strTable，strPathFile，blnHasFieldNames 
 
'如果要删除
'EXCEL文件，请取消注释下一个代码步骤导入后
'Kill strPathFile 
 
 strFile = Dir（）
循环
  

I posted this question yesterday, but people advised me to ask again, with more detail.

I have several (+1000) raw files that I need to convert, and eventually import into Access

. I've written an Excel macro that will do that. It's fairly extensive, and has a great deal of operations, but the 'gist' of it is below. It asks a user to select some files, then saves the files to the same directory as the original raw files. It's called ConvertWithDialog, and in the interest of saving space, is in the Paste below.

<script src="https://pastebin.com/embed_js/2cfKLbAr"></script>

I also have a version without the dialog, which can be run by passing file(s) to it through a separate function.

<script src="https://pastebin.com/embed_js/9AB8VKgL"></script>

My issue is importing to Access. How do I convert the multiple files and import to Access in one step(one button press in Access)? I don't want to have to run the convert function, wait for the files to finish converting, then import to Access. I tried this, as suggested by a user previously, but when I added the arguments as demonstrated, VBA threw an error, saying a '=' was expected. I've a macro to batch import Access files, and can include it. but it's not very elegant, and I'm sure I'll have to do it differently in the end anyway.

解决方案

Import Data from All EXCEL Files in a single Folder via TransferSpreadsheet (VBA)

Generic code to import the data from the first (or only) worksheet in all EXCEL files that are located within a single folder. All of the EXCEL files' worksheets must have the data in the same layout and format.

Dim strPathFile As String, strFile As String, strPath As String
 Dim strTable As String
 Dim blnHasFieldNames As Boolean

' Change this next line to True if the first row in EXCEL worksheet
 ' has field names
 blnHasFieldNames = False

' Replace C:\Documents\ with the real path to the folder that
 ' contains the EXCEL files
 strPath = "C:\Documents\"

' Replace tablename with the real name of the table into which 
 ' the data are to be imported
 strTable = "tablename"

 strFile = Dir(strPath & "*.xls")
 Do While Len(strFile) > 0
       strPathFile = strPath & strFile
       DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
             strTable, strPathFile, blnHasFieldNames

' Uncomment out the next code step if you want to delete the 
 ' EXCEL file after it's been imported
 '       Kill strPathFile

       strFile = Dir()
 Loop

这篇关于如何转换Excel文件，然后一步导入到Access？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！