Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1355
  • Last Modified:

Open a fixed width file from VB.Net

When you record opening a file from Excel in VBA to open up a file fixed width, it gives you:

Workbooks.OpenText(Filename"c:\blahblah", Origin:=437, StartRow _
:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(8, 1), Array( _
19, 1), Array(27, 1), Array(45, 1), Array(54, 1), Array(71, 1), Array(88, 1), Array(99, 1), _
Array(108, 1), Array(116, 1), Array(131, 1)), TrailingMinusNumbers:=True

When I bring it over to VB.Net I make it:

oXl.Workbooks.OpenText(Filename:="" & ImportFiles(x - 1) & "", Origin:=437, StartRow _
:=1, DataType:=xl.XlTextParsingType.xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(8, 1), Array( _
19, 1), Array(27, 1), Array(45, 1), Array(54, 1), Array(71, 1), Array(88, 1), Array(99, 1), _
Array(108, 1), Array(116, 1), Array(131, 1)), TrailingMinusNumbers:=True

Up top I have :
Imports xl = Excel

and at the top of the sub I have:
oXl = CreateObject("Excel.Application")

I've added a refernce to the Excel 10.0 object library.

It dosen't like the word "Array" and even when I added Imports Arr = System.Array and used different variations of the Arr, it still wouldn't do it.  I really just need to open a file with fixed width.  Am I missing something?

Thanks,

John
0
burgerjo
Asked:
burgerjo
  • 3
  • 2
  • 2
1 Solution
 
TertioptusCommented:
Check this out!

http://www.codeproject.com/vb/net/TextFileParser.asp

probably better than using excel
0
 
burgerjoAuthor Commented:
That looks good, but I want to put this out in Excel if at all possible.
0
 
TertioptusCommented:
well then

check this out


http://gotdotnet.com/Community/MessageBoard/Thread.aspx?id=363074&Page=1#363383

this seems to be a common problem.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
burgerjoAuthor Commented:
That seems to be using XlTextParsingType.xlDelimited, and is concerned about formatting the fields.  I really just need to specify the column widths in an array, like the Excel open in VBA does with xlFixedWidth.  I hope I'm not getting confusing.
0
 
cavehopCommented:
I see two issues.

1. Probably just a copy/paste typo, but the OpenText command is missing its closing )

2. Array: Yes, it is the base class for all array classes in the CLR, but other than possibly using some of its shared methods for working with arrays, arrays are handled through the language implementation.  So try creating and initializing a regular two-dimensional array of integers and using that instead.

Example:
Dim fieldInfoAry(,) As Integer = {{0, 1}, {8, 1}, {19, 1}, {27, 1}, {45, 1}, {54, 1}, {71, 1}, {88, 1}, {99, 1}, {108, 1}, {116, 1}, {131, 1}}

oXl.Workbooks.OpenText(Filename:="" & ImportFiles(x - 1) & "", Origin:=437, StartRow :=1, _
DataType:=xl.XlTextParsingType.xlFixedWidth, FieldInfo:=fieldInfoAry, _
TrailingMinusNumbers:=True)

Hope this helps,
--Lance--
0
 
cavehopCommented:
Oh and yes, this ~is~ a good example of one of those VBA/VB6 to VB.NET gotchas...
0
 
burgerjoAuthor Commented:
That was it !!!!!  Thanks alot for that.   I can use this array logic in may different spots.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now