PDA

View Full Version : Sleeper: Transferring date and text data to an excel file



martha555
07-05-2005, 11:53 PM
I want copy date and text data types which is stored in an excel file(temp.xls) into a new excel file i.e.,my current file. How do I go about it.

doctordoggie
07-08-2005, 01:24 AM
I'm assuming you are having problems when you are copying cell values over - dates turn into numbers and text-formatted numbers become numbers.

This command will format a cell as text (do this before you copy anything into it to be safe)

Thisworkbook.Activesheet.Range("A1:IV65536").NumberFormat = "@"

This formats a cell as a date:

Thisworkbook.Activesheet.Range("A1:IV65536").NumberFormat = "m/d/yyyy"

Hope this is what you wanted.

Neil

Stead
07-09-2005, 03:22 AM
I've had a similar problem with pesky dates

what i use to get around it is (desitnation) cells.numberformat = (source) cells.numberformat


Workbooks.Open "test.xls"
Workbooks(ActiveWorkbook.Name).Sheets("Sheet1").Cells(1, "A").NumberFormat = _
Workbooks("test.xls").Sheets("Sheet1").Cells(1, "A").NumberFormat
Workbooks(ActiveWorkbook.Name).Sheets("Sheet1").Cells(1, "A") = _
Workbooks("test.xls").Sheets("Sheet1").Cells(1, "A")

i'm not sure if that would work as i didn't test it, but basically that should open a workbook called test.xls

copy the format of the cell from
test.xls, sheet1, cell A1
to
activeworkbook, sheet1, cell A1

then copy over the value, changing the sheets, workbooks etc to yours should work, this is what i have done when copying over dates anyway, worked for me!