My application opens a CSV file in Excel but because of some of the values it reads the values incorrectly. For example, if you open the CSV file in notepad there is a value '71902E10' that translates to 7.19E+14 or 719020000000000. How can I get it to translate properly to 71902E10. Can I format the cells before it opens? Or should I open it in notepad and process that way. If so how can I open a csv file in notepad when it automatically wants to open in excel? I'd rather be able to format and use excel if possible...

For the second part, right-click the file, and select notepad from the Send To list.
Also, of course, if Notepad is open, you can drag the file out of Windows Explorer (or off the DeskTop) and drop it onto the running instance of Notepad.

Where is this .csv coming from? (Do you have any control over the creation process?)

You say, “ . . . is a value '71902E10' that translates . . . ”
Are those “'”s ([u]'</u>71902E10[u]'</u>) contained in the .csv, or did you add them for clarity in reading your post here?

I'm hoping to be able to automatically open the file using my VB application.

I am using FileToOpen = Application.GetOpenFilename to bring up the explorer to search for the csv. The csv comes out of a different application so I have no control over the file. But I was hoping to open it up NOT in excel but any other application (like an open as command). The .csv files are all named differently so there is no set filename.

I think I may be able to use the word object to open the csv in word but when I try to open using word, it opens every other time. sometimes it just doesn't open, othertimes it does. Yet when I go to the task manager there are several WINWORD.exe's.

Yes I added the "'"s for clarity sake. There aren't actually quotations.

If you know where those fields are within the file, you should be able to pre-process the info before sending it to Excel. Add 1 single-quote before the string of characters to explicitly inform Excel that it is to be treated as character data, not numeric.

There wa an issue regarding Word as an automation server. It has been awhile, but if memory serves, you must [u]explicitly</u> close Word after opening it from within another application.

There will be Microsoft-published fixes regarding this at their website, I'm sure.

So this is my form_load function (it calls the crossing files function that does all of the work). I guess I don't know how to open the csv and preformat the file before opening it in Excel. Maybe you can point me in the right direction? Here is my code:

Dim FileToOpen As String

Private Sub Form_Load()

    Dim MyXL As Excel.Application 'create MyXL
    Set MyXL = New Excel.Application
    Dim WkbkName As String

    On Error GoTo ErrorHandler
        Set MyXL = GetObject(, "Excel.Application")

        If Err.Number = 429 Then
            Set appExcel = CreateObject("Excel.Application")
        End If


    FileToOpen = Application.GetOpenFilename 'Opens up explorer

    If FileToOpen = "False" Or FileToOpen = "" Then
        If MsgBox("Do you want to quit application", vbYesNo) = vbYes Then
            Set MyXL = Nothing
            Unload OpenFile
            Exit Sub
        Else
            MsgBox ("Please relaunch application")
            Set MyXL = Nothing
            Unload OpenFile
            Exit Sub
        End If

    Else
        Workbooks.Open filename:=FileToOpen 'Opens up the file selected
        Application.Visible = True 'in Excel
    End If


    Application.ActiveWorkbook.Save

    Call CrossingFiles 'Calls function

    Application.ActiveWorkbook.Save
    Application.DisplayAlerts = False
    Application.ActiveWorkbook.Close

    Set MyXL = Nothing

    Unload OpenFile

ErrorHandler: ' Error-handling routine.
Dim StrErr As String
StrErr = Err.Number & " - " & Err.Description
    If Err = 364 Then
      Exit Sub
    End If

End Sub

For readability, I would have Since you are just opening the .csv in Excel, preformatting isn't actually an option.
What I was thinking is that you would open Excel, open the file in VB, read the file (adjusting the values as nec.), then write the values into the open, new workbook.

Perhaps Excel keeps the original values that generate the cell display, and you could use vb (or VBA within Excel) to traipse over the whole range of what was brought in, and if it finds the sort of format that you described initially, prefix a single quote to the beginning of it. (If you know exactly where these values come in—which column—you could process juset that column.)

Thanks for your advice on the top portion. I will make those chagnes.

As far as the rest, I like the idea of opening the file in VB, reading the file and putting the values into a new workbook...however how can I open a file that I don't know what the file will be? This is the one part I am confused on.

I currently use:

FileToOpen = Application.GetOpenFilename 'Opens up explorer

to determine the file name. But because it is the GetOpenFilename it opens the filename and I only want to get the file name. If I still use this how can I close excel and then use the FileToOpen to process the records? Is this possible? I've only figured out how to close the workbook. I want to close the entire Excel application because I don't need it any longer.