Copying values

[gscarter]

Hello,

Using the code below i am copying values from two worksheets into one.

Private Sub CopyData(path1, path2)

Set wb1 = Workbooks.Open(path1, True, True)
Set wb2 = Workbooks.Open(path2, True, True)

With ThisWorkbook.Worksheets("Sheet1")
        .Columns("A:B").Value = wb1.Worksheets(sheetName(TextBox1)).Columns("A:B").Value
        .Columns("D:E").Value = wb2.Worksheets(sheetName(TextBox2)).Columns("A:B").Value
End With

End Sub

The problem i am having is when i copy over any values with 'E's in it is copying them as exponentials rather than just E's. For example:

5697E93

will copy as:

5.70E+96

Anyone got any ideas?

Ive tried using .formula and .text instead of .value.
.formula copied the same
.text copied no values

Any fixes need to be coded, as i do not create the spreadsheets, this is the format they are given to me.

I am using Microsoft Office XP (2002) Excel spreadsheets

Hope you can help
Gary

[grichey]

can you do just change formatcells for all cells to text?

[Bob Phillips]

Is this approach acceptable?

[vba]

Range("B1").Value = IIf(TypeName(Range("A1").Value) = "String", "'", "") & Range("A1").Value
[/vba]

[gscarter]

can you do just change formatcells for all cells to text?

I've tried this, when i select general it leaves it in the second format, when i select number it turns it into a huge number because it is picking up the E as exponential.

[gscarter]

Is this approach acceptable?

[vba]

Range("B1").Value = IIf(TypeName(Range("A1").Value) = "String", "'", "") & Range("A1").Value
[/vba]

Hi thanks for the quick reply.

Sorry, i dont fully understand what that code does, does it only copy values that are stored as string?

And doing it like that would i have to copy each individual record one by one?

Thanks for your help

[Bob Phillips]

Not, it precedes a string value with an apostophe, stopping Excel converting to an number.