在VBA中使用FieldInfo动态格式化Workbook.OpenText上的列 [英] Dynamically format columns on Workbook.OpenText using FieldInfo in VBA

问题描述

我正在用VBA编写程序以打开一个文本文件.我想将单元格 format 格式化为文本 open 以保留数据(问题:长数字字符串将转换为数字并截断).

I'm writing a program in VBA to open a text file. I want to format the cells to text on open to perserve data (issue: long number strings are being converted to numbers and truncate).

通过研究，我了解到可以使用 .OpenText 的 FieldInfo 参数完成此操作.

Through research I've learned this can be done using the FieldInfo argument for .OpenText.

当前在互联网上运行的所有示例都是静态的，需要程序员在 FieldInfo:= Array(Array(1,2(2)，Array(2,2)..... Array(n，2).

All current examples working on the internet are static and require the programmer to hard code in FieldInfo:=Array(Array(1,2),Array(2,2).....Array(n,2).

这是我最接近答案的方法，但这也不起作用. http://www.excelforum.com/excel-general/522433-workbooks-opentext-method-fieldinfo-parameter.html .它给我一个 1004 错误.这是我的版本:

Here is the closest I got to an answer but this doesn't work either. http://www.excelforum.com/excel-general/522433-workbooks-opentext-method-fieldinfo-parameter.html. It gives me a 1004 error. Here is my version:

Dim colInfo(1 To 1000,1 To 2)
For i = 1 To 1000
    colInfo(i , 1) = i
    colInfo(i , 2) = 2
Next i

我在 Array 上找不到任何vba文档.如果我能弄清楚如何创建 Array 变量而不是 Dim x()as y ，则可以解决此问题.任何帮助将不胜感激！

I can't find any vba documentation on Array. If I could figure out how to create an Array variable NOT a Dim x() As y, I could solve this issue. Any help would be greatly appreciated!

推荐答案

Array函数仅返回一个变体-这只是一种快速的初始化方法.因此，您可以只创建其中之一，然后将其作为参数传递.例如:

The Array function just returns a variant - it is just a quick way of initialising. So you could just create one of those instead and pass that in as your argument. For example:

Sub abc()

Dim v1 As Variant, v2 As Variant

v1 = Array(1, 2, 3)

ReDim v2(3)
v2(0) = 1
v2(1) = 2
v2(2) = 3

MsgBox v1(0) & vbTab & v2(0) & vbCr & v1(1) & vbTab & v2(1) & vbCr & v1(2) & vbTab & v2(2)

End Sub

这两个数组(v1和v2)相同.

These two arrays (v1 and v2) are the same.

我希望这会有所帮助.

这篇关于在VBA中使用FieldInfo动态格式化Workbook.OpenText上的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！