Importing csv to excel - text formating

Hello,
Can you please help,
I'm trying to Copy data from a csv file File to my Excel Sheet. (Excel 2007)
Trying to Copy Columns A-AZ.
I formatted Column BD on my excel sheet, as text before Copying from the csv file.
Still, the numbers are showing as +11, +21,....
if I format Column BD as Custom 0, the numbers are showing with trailing zeroes.
9.61102E+21    6.48029E+11      7.75632E+11    7.756E+11    1.9747E+14

This is the code I'm using.

Dim fd As FileDialog
Dim strWorkbookName As String
 Set fd = Application.FileDialog(msoFileDialogFilePicker)
 With fd
      .InitialFileName = "TEXT;C:\Users\Wass\Desktop\**.*"
     .Filters.Clear
     .Filters.Add "", "*.csv", 1
     If .Show = -1 Then
        Workbooks.Open Filename:=Trim(.SelectedItems.Item(1))
        strWorkbookName = ActiveWorkbook.Name

'Copy Range
Range("A1:AZ25000").Copy

'''Paste
    Windows("Ground.xls").Activate
    Sheets("Orders").Range("BC1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    Application.CutCopyMode = False
    Windows(strWorkbookName).Activate
    ActiveWindow.Close
    End If
 End With

Your help is greatly appreciated,
W.E.BAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Wayne Taylor (webtubbs)Connect With a Mentor AstronautCommented:
You use the import instead of opening the CSV file....

    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    fd.InitialFileName = "TEXT;C:\Users\Wassim\Desktop\**.*"
    fd.Filters.Clear
    fd.Filters.Add "", "*.csv", 1
    If fd.Show = -1 Then
        With ActiveSheet.QueryTables.Add(Connection:= _
              "TEXT;" & fd.SelectedItems.Item(1), Destination:=Range("$BC$1"))
             .FieldNames = True
             .RowNumbers = False
             .FillAdjacentFormulas = False
             .PreserveFormatting = True
             .RefreshOnFileOpen = False
             .RefreshStyle = xlInsertDeleteCells
             .SavePassword = False
             .SaveData = True
             .AdjustColumnWidth = True
             .RefreshPeriod = 0
             .TextFilePromptOnRefresh = False
             .TextFilePlatform = 437
             .TextFileStartRow = 1
             .TextFileParseType = xlDelimited
             .TextFileTextQualifier = xlTextQualifierDoubleQuote
             .TextFileConsecutiveDelimiter = False
             .TextFileTabDelimiter = True
             .TextFileSemicolonDelimiter = False
             .TextFileCommaDelimiter = True
             .TextFileSpaceDelimiter = False
             .TextFileColumnDataTypes = Array(1, 2, 1, 1, 1, 1)
             .TextFileTrailingMinusNumbers = True
             .Refresh BackgroundQuery:=False
        End With
    End If

Open in new window

0
 
Wayne Taylor (webtubbs)AstronautCommented:
What do the values in the CSV file look like?
0
 
W.E.BAuthor Commented:
If I open the csv in note Pad, I see the full numbers.
12 digits up to 24 digits.

if I open the csv file, I see same as Excel.
9.61102E+21
6.48029E+11
6.64773E+11
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
NorieVBA ExpertCommented:
What happens if you use Workbooks.OpenText instead of just Workbooks.Open?
0
 
Wayne Taylor (webtubbs)AstronautCommented:
Excel has a maximum 15 digits of precision, so when you open the CSV file in Excel, it will be converted to Scientific notation, with the remaining digits > 15 removed. Formatting as number doesn't help, as there is still only 15 digits of precision in the value.

The work around for your instance is to not open the CSV file in Excel, but import the data as Text. Record a macro while going to Data > From Text. Select Comma as the delimiter then select Text format on the appropriate data column.
1
 
W.E.BAuthor Commented:
Hi Wayne,
this is what I've been doing for a long time.
but, now I need to incorporate this into a bigger macro that I need to give to some of our Employees to use.
I was hoping for a solution that don't require any additional steps.

thanks.
0
 
Wayne Taylor (webtubbs)AstronautCommented:
That's why I suggested recording a macro while doing the import. Incorporate the code from the recorded macro into your bigger macro.
0
 
W.E.BAuthor Commented:
thanks for your help,

how do I change this
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\Wassim\Desktop\orders", Destination:=Range("$BC$2"))
        .Name = "orders" ...........................

To allow me to choose the file
Application.FileDialog(msoFileDialogFilePicker)

thanks again.
0
 
Wayne Taylor (webtubbs)AstronautCommented:
With ActiveSheet.QueryTables.Add(Connection:= _
         "TEXT;" & fd.SelectedItems.Item(1) & ", Destination:=Range("$BC$2"))

Open in new window

0
 
W.E.BAuthor Commented:
Invalid Character error
Destination:=Range("$BC$2"))
0
 
Wayne Taylor (webtubbs)AstronautCommented:
Sorry, should actually be this...
With ActiveSheet.QueryTables.Add(Connection:= _
         "TEXT;" & fd.SelectedItems.Item(1), Destination:=Range("$BC$2"))

Open in new window

0
 
W.E.BAuthor Commented:
Hi Wayne,
Sorry,
It's not doing anything,
it's only  opening the csv file

Dim fd As FileDialog
Dim strWorkbookName As String
 Set fd = Application.FileDialog(msoFileDialogFilePicker)
 With fd
      .InitialFileName = "TEXT;C:\Users\Wassim\Desktop\**.*"
     .Filters.Clear
     .Filters.Add "", "*.csv", 1
     If .Show = -1 Then
        Workbooks.Open Filename:=Trim(.SelectedItems.Item(1))
        strWorkbookName = ActiveWorkbook.Name
    End If
With ActiveSheet.QueryTables.Add(Connection:= _
         "TEXT;" & fd.SelectedItems.Item(1), Destination:=Range("$BC$1"))
         ''''''''''''''''''''''''''''' .Name = Trim(.SelectedItems.Item(1)) '''''''''''''''''''''''''''''''''''''''''''''''
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 2, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False

        End With
        End With
0
 
SunBowCommented:
Ditto: Wayne Taylor  (webtubbs)
0
 
W.E.BAuthor Commented:
Thank you very much Wayne.
Your help is greatly appreciated.
0
All Courses

From novice to tech pro — start learning today.