VBA - Fixed Length

[shemayisroel]

Hi,

I have a set of data that's fixed length - 19 characters per field with 17 Headers. Data structure is below. Each record comes over 3 rows as defined below and not all fields for every record will contain data. Looking for a VBA code that can extract the data and place each field (header) in a seperate column. This can be achieved (and have done so) via the "Text to Columns" but looking to now automate this via VBA.

<TABLE style="WIDTH: 928pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1237><COLGROUP><COL style="WIDTH: 928pt; mso-width-source: userset; mso-width-alt: 45238" width=1237><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 928pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=1237>Field1 Field2 Field3 Field4 Field5 Field6 Field7 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20>Field8 Field9 Field10 Field11 Field12 Field13 Field14 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20>Field15 Field16 Field17 </TD></TR></TBODY></TABLE>

 

[dk]

Is the space character the delimiter for your data?

 

[shemayisroel]

Is the space character the delimiter for your data?

Thanks for the reply, no. It's fixed length, would probably not use space as some of the fields will contain a suburb with a space in it like Mount Lewis etc

Does that answer your question?

 

[dk]

Yes it does answer my question. What is your source data - is it a text file or is it already in Excel? I would be fairly confident that TextToColumns would be the quickest way to do this (rather than coming up with some elaborate array based method). Have you tried just recording a macro to do the text to columns? What are the problems you're facing with that?

Sorry for all the questions but just need a bit more background on exactly what's needed.

 

[shemayisroel]

Source data is a text file, copying that into excel.

Your right, simply recording the text to columns and manually adjusting the break line does it.

Thanks.

I'll attach that code to a command button then that should do it.

Is there a better/cleaner way?

 

[dk]

You could loop through the file using Scripting.FileSystemObject and then parse the data using something like Mid$ but that seems un-necessary if your data can be imported directly using TextToColumns. In the past I've used the FSO method but that was where the data had lots of rows that I wanted to exclude e.g. header rows, subtotals, etc.

If you're creating a macro that is going to be used by others and you want it to look more "polished" then you could disable screen updating using Application.ScreenUpdating = False so they don't actually see what's happening. Just make sure you set it back afterwards otherwise Excel will be unusable.

HTH
DK