pipe delimited 2

[dodge20]

Is it possible to save an excel worksheet with | as the delimiter? Dodge20
If it ain't broke, don't fix it.

 

[dodge20]

I am having a problem with a 0 in front a a number. I have account numbers, and some of them start with a zero. Excel Automatically removes those zeros. I change the format of the cells, so it allows the correct number of zero's, but when I run this macro, they are gone. Any ideas?

Dodge20
If it ain't broke, don't fix it.

 

[bryanbayfield]

try format(activesheet.cells(r,c),"@&quot

 

[dodge20]

Nope, didn't work Dodge20
If it ain't broke, don't fix it.

 

[RobBroekhuis]

Use the same numberformat string that you specified for the cells in the spreadsheet in the print# statement, e.g.

print#1, format(activesheet.cells(r,c),"0000000" "|";
Rob

 

[dodge20]

Won't that change every column? I just want column F, if there is a way to do that. Dodge20

 

[bryanbayfield]

for c=1 to .column+.columns.count-2
          select case c
          case 6
          print#1, format(activesheet.cells(r,c),"0000000"); "|";
          case else
          print#1, format(activesheet.cells(r,c)); "|";
      next c

One thing I don't understand is why Rob is using

for c=1 to .column+.columns.count-2

- it's perfectly good code, but it's be easier to follow if he used

for c = 1 to .columns.count -1

since by starting the loop at column C = 1, he's assuming that the first cell to be written to file in each row appears in column "A".

he's assumed all your data starts in column "A", but if you look at the exterior loop he's not assumed that your data starts in row 1 - I can't see why - maybe I've missed something?

Also, just out of interest, how many rows do you have, or are likely to have? If you have more than 32,000 the macro will fall over because integers can't handle numbers much bigger than that. I can't tell from your post whether you're likely to have this many rows or not.

 

[bryanbayfield]

aw sugar, just before the "next" you need to insert a line saying "end select".

D'oh!

 

[dodge20]

Thanks, it worked. You get a star. Dodge20

 

[dodge20]

I think the c=1 to .column+.columns.count-2 allows blank columns to precede any data. I need column A to be blank, and this allow me to do so. Dodge20