在Excel中，使用VBA-如何从定界文件导入和插入数据? (示例代码和数据) [英] In Excel, using VBA -- how do I import and insert data from a delimited file? (sample code & data)

问题描述

我已经使用以下VBA设置了工作表:

I've got a sheet setup with the following VBA:

Sub PrintPDF()

Application.DisplayAlerts = False

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\<insert_username>\Desktop\macro\Book1.pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False _

Application.Quit

End Sub

(基本上打开后会打印该文件的PDF，然后关闭excel)

(which basically opens prints a PDF of the file and closes excel)

然后我在工作簿中找到了这个

Then I've got this in the workbook:

Private Sub Workbook_Open()
Run "PrintPDF"
End Sub 

(它在加载时"运行PrintPDF宏-意味着打开excel文件时的第一件事.)

(which runs the PrintPDF macro "on load" -- meaning when first thing when the excel file is opened.)

因此，我现在想完成该任务所缺少的是一些不错的干净代码，可以在加载时"导入定界文件并将其映射到单张纸中的固定点.

So, what I'm missing now to finish out this task is some nice clean code to import a delimited file "on load" and map it to a fixed point in a single sheet.

ID<TAB>Name<TAB>Location
1<TAB>John<TAB>US
2<TAB>Mike<TAB>CN
3<TAB>Tom<TAB>CA

示例Excel行(在加载时"插入并映射到单元格之后):

<A1>null<B1>null<C1>null<D1>null
<A2>null<B2>ID<C2>Name<D2>Location
<A3>null<B3>1<C3>John<D3>US
<A4>null<B4>2<C4>Mike<D4>CN
<A5>null<B5>3<C5>Tom<D5>CA

(偏移数据映射，因为我想确保能够将导入的数据映射到excel文件中的任何位置，只要两端的行数和列数都匹配；两端是excel文件和带分隔符的数据源文件；它们将为1比1.)

(offset the data mapping because I'd like to make sure that I'm able to map the imported data to any where in the excel file as long as the row and column count match in both ends; both ends being the excel file and the delimited data source file; which they will 1-for-1.)

如果您有任何疑问，请告诉我.目标系统是Window 7(Office 2010)或Mac 10.5(Office 2011)-上面的代码适用于Windows，但是我认为唯一的区别是文件名代码，即:"C:\Users\<insert_username>\Desktop\macro\Book1.pdf"

If you have any questions, let me know. The target system is Window 7 (Office 2010) or Mac 10.5 (Office 2011) -- the code above is for Windows, but the only difference I believe would be the filename code, that being: "C:\Users\<insert_username>\Desktop\macro\Book1.pdf"

这是我到目前为止的代码，有什么毛病:

Here's the code I have so far, anything wrong with it:

Sub ImportCSV()
'
' ImportCSV Macro
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\<insert_username>\Desktop\macro\sample_pipeline_data.txt", Destination:= _
        Range("$B$2"))
        .Name = "sample_pipeline_data"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

推荐答案

VBA张贴在我的问题上方，但简单的答案是，您可以使用内置的PDF函数记录该宏，以自动执行此操作.我建议的一件事是，您将在运行该平台的平台上测试此marco(或任何marco).例如，在上面的VBA代码中，该代码是使用Windows 7上的Office 2010生成的-如果未在调试器中运行它并删除Mac版本的Office不支持的一些属性，它将无法在Mac 10.5(Office 2011)上运行. (尽管根据我的测试，Window的版本也很可能不支持它们，但是请不要抛出错误.)如果您对代码/答案有疑问，请注释一下，我已经很高兴提供任何其他信息.

VBA is posted above in the body of my question, but the simple answer is that you can just record a macro using the built in PDF functions to automate this. One thing I would suggest is that you test this marco (or any marco) on the platforms it will be running on. For example, in the VBA code above which was generated using Office 2010 on Windows 7 -- it will not work on Mac 10.5 (Office 2011) without running it in the debugger and deleting a few attributes that Mac's version of office doesn't support (though based on my testing, it appears very likely that they're not supported by the Window's version either, but just don't throw errors.) If you ever have questions about the code/answer -- just comment and I've be happy to provide any additional info.

这篇关于在Excel中，使用VBA-如何从定界文件导入和插入数据? (示例代码和数据)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！