captbluefin
12-15-2017, 08:45 AM
Here is a simple example of the sheet layout. Three rows and at most 5 columns with data.
A B C
D E F G
H I J K L
We save the sheet into a tab delimited text file.
The text file ends up as 5 columns by 3.
The problem is when we go to upload the tab delimited text file to the receiving authority.
We get an error pertaining to the number of columns in rows 1 and 2.
The first row has data in 3 columns. However the tab delimited text file has tabs for the "empty" 4th and 5th column of row 1.
The second row has data in 4 columns and an extra tab for the "empty" 5th column.
All the rows after row 3 have data in all 5 columns.
We can manually delete the tabs from the text file, and all is good with the upload.
Obviously, we would like to avoid the need to manually edit the text file.
The only way we have been able to think of to accomplish what we want is to split the information onto 3 sheets. One sheet with row 1 that has only 3 columns, sheet2 with row 2 that has 4 columns, and a 3rd sheet that has all the rest of the rows that have 5 columns. Then save 3 text files. Then copy the 3 files into one file. Obviously a kludge at best. Never mind we are clueless at how to run a dos command prompt using variables from within Excel VBA.
Here's an example of something that on the surface should be simple, but in practice is an issue.
As always, any ideas are greatly appreciated.
A B C
D E F G
H I J K L
We save the sheet into a tab delimited text file.
The text file ends up as 5 columns by 3.
The problem is when we go to upload the tab delimited text file to the receiving authority.
We get an error pertaining to the number of columns in rows 1 and 2.
The first row has data in 3 columns. However the tab delimited text file has tabs for the "empty" 4th and 5th column of row 1.
The second row has data in 4 columns and an extra tab for the "empty" 5th column.
All the rows after row 3 have data in all 5 columns.
We can manually delete the tabs from the text file, and all is good with the upload.
Obviously, we would like to avoid the need to manually edit the text file.
The only way we have been able to think of to accomplish what we want is to split the information onto 3 sheets. One sheet with row 1 that has only 3 columns, sheet2 with row 2 that has 4 columns, and a 3rd sheet that has all the rest of the rows that have 5 columns. Then save 3 text files. Then copy the 3 files into one file. Obviously a kludge at best. Never mind we are clueless at how to run a dos command prompt using variables from within Excel VBA.
Here's an example of something that on the surface should be simple, but in practice is an issue.
As always, any ideas are greatly appreciated.