cell formatting

[Thai]

Is there a way to change EVERY cell in an Excel spreadsheet to "General" format in one shot in vb code? If you open the spreadsheet, you can hit CONTROL-A to select all then change it to General... is there a way to do that in code? The reason I am asking this is because I have a spreadsheet with text in it, but the cells are formatted as "Number" and when I open it with ADO, those cells become NULL in the recordset. Please help if you can!

Thanks,
Thai

[JHausmann]

Record an Excel macro, doing the function you want, and use the code generated by it.

[Thai]

Hrm I have never used an Excel macro.. are you saying I can record a macro then put the code it generates into VB and vb will do exactly what I did to the excel file when I run it in vb?

Thanks for bearing with me ,
Thai

[Nitro]

Use CELLS.NumberFormat= "General"

CELLS represent control-A

[JHausmann]

Originally posted by Thai
Hrm I have never used an Excel macro.. are you saying I can record a macro then put the code it generates into VB and vb will do exactly what I did to the excel file when I run it in vb?

Thanks for bearing with me ,
Thai

Yep. You will need to modify the generated code (make it point to the correct VB objects) but this is the quickest way to do Excel stuff in VB (especially if you don't know much about Excel, you get someone who does to make you a Macro and then move the code).

[Thai]

Cool I did it and it worked.. thanks so much. One more question if you can.. After I converted it all to Text format, and then did a SELECT INTO to import it to Access, one column because a "Memo" type in the Access table, but a column similiar to it did NOT, so one record that column got truncated. How does it know what to make memo and what not to make memo?

Thanks,
Thai

[JHausmann]

Just guessing but I imagine it has something to do with the size of the column(length in chars ). If the cell with the most data in it is larger than can fit in a char field, it will make that column a memo field

[Thai]

Yeah, I had originally thought that too but I set both of the column's widths to 10 and still only one went to Memo in Access.... wierd.

Thai