keep formatting
 

I am copying a large range, nealy 60K rows. One of the colums has a 15 digit
number in it. The destination cell is pre-formatted as text, and I want to
keep it that way. However when I copy the range from the CSV file to the
template, the formatting goes with it. Code below, how can I maintain the
formatting I have in the destination sheet?

Dim iLastRow As Long
Dim sFile1 As String
Dim wb, wkbkCSV As Workbook
Dim rDestCell As Range

sFile1 = Application.GetOpenFilename("CSV Files, *.csv")
If sFile1 = "False" Then
Exit Sub
End If

Set rDestCell = Workbooks("Sessions.xlsm").Worksheets("Sheet1").Ra nge("A2")
Set wkbkCSV = Workbooks.Open(Filename:=sFile1)

With wkbkCSV
.Worksheets(1).UsedRange.Copy Destination:=rDestCell
.Close savechanges:=False
End With

keep formatting
 

I think PasteSpecial will do that for you. Try this snippet of code:

'Select your data to copy...
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Make sure you have a backup of your date...in case the code does something
you do NOT want.

Regards,
Ryan---

--
RyGuy


"Striker" wrote:

I am copying a large range, nealy 60K rows. One of the colums has a 15 digit
number in it. The destination cell is pre-formatted as text, and I want to
keep it that way. However when I copy the range from the CSV file to the
template, the formatting goes with it. Code below, how can I maintain the
formatting I have in the destination sheet?

Dim iLastRow As Long
Dim sFile1 As String
Dim wb, wkbkCSV As Workbook
Dim rDestCell As Range

sFile1 = Application.GetOpenFilename("CSV Files, *.csv")
If sFile1 = "False" Then
Exit Sub
End If

Set rDestCell = Workbooks("Sessions.xlsm").Worksheets("Sheet1").Ra nge("A2")
Set wkbkCSV = Workbooks.Open(Filename:=sFile1)

With wkbkCSV
.Worksheets(1).UsedRange.Copy Destination:=rDestCell
.Close savechanges:=False
End With

keep formatting
 

CSV files don't remember formatting. They're just plain old text files.

When you open the .CSV file, then excel will treat anything that looks like a
number as a number.

A couple of options are to rename the .csv file to .txt and bring in the file
and control how each field should be treated. (Record a macro to get the syntax
you need.)

Or you can bring in the data by using Data|Import external data|Import data

But you'll want to record a macro when you do that manually, too. That way
you'll be able to specify the format of each field.

=======
On the other hand, you could let excel treat the value as a number, do the
copy|paste and then format that column as text and use data|text to columns to
convert those values to text.

If you have 15 digit numbers that have leading 0's, then you'll have to do
something else (like loop through each cell).

Striker wrote:

I am copying a large range, nealy 60K rows. One of the colums has a 15 digit
number in it. The destination cell is pre-formatted as text, and I want to
keep it that way. However when I copy the range from the CSV file to the
template, the formatting goes with it. Code below, how can I maintain the
formatting I have in the destination sheet?

Dim iLastRow As Long
Dim sFile1 As String
Dim wb, wkbkCSV As Workbook
Dim rDestCell As Range

sFile1 = Application.GetOpenFilename("CSV Files, *.csv")
If sFile1 = "False" Then
Exit Sub
End If

Set rDestCell = Workbooks("Sessions.xlsm").Worksheets("Sheet1").Ra nge("A2")
Set wkbkCSV = Workbooks.Open(Filename:=sFile1)

With wkbkCSV
.Worksheets(1).UsedRange.Copy Destination:=rDestCell
.Close savechanges:=False
End With

--

Dave Peterson