Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > VB How-To
|
VB How-To Ask your "How do I do this with VB?" questions in this forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB How-To section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 21st, 2005, 08:45 AM
Authorized User
 
Join Date: Apr 2005
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default VB: Opening CSV file in Excel versus notepad

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...
 
Old April 21st, 2005, 02:50 PM
Friend of Wrox
 
Join Date: Nov 2004
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

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?
 
Old April 21st, 2005, 03:00 PM
Authorized User
 
Join Date: Apr 2005
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old April 21st, 2005, 03:04 PM
Friend of Wrox
 
Join Date: Nov 2004
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

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.
 
Old April 22nd, 2005, 08:30 AM
Authorized User
 
Join Date: Apr 2005
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old April 22nd, 2005, 10:54 AM
Friend of Wrox
 
Join Date: Nov 2004
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

For readability, I would have
Code:
    Unload OpenFile

    Exit Sub           ' Makes the intent more clear (to me, anyway)

ErrorHandler:          ' Error-handling routine.
    Dim StrErr As String
    StrErr = Err.Number & " - " & Err.Description
    If Err = 364 Then  ' This is a bit unnec., as you are going to be leaving the sub immediately anyway.
      Exit Sub 
    End If

End Sub
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.)
 
Old April 22nd, 2005, 10:59 AM
Authorized User
 
Join Date: Apr 2005
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem in opening excel file in MS Excel 2000 kallol Visual C++ 0 November 16th, 2007 05:48 AM
Export to Excel as .csv file from JSP naheedv Reporting Services 3 November 30th, 2006 08:04 AM
Excel Vs CSV file - import performance itHighway Classic ASP Databases 0 August 5th, 2006 03:40 PM
How To Export a specific Excel Sheet as a csv file mrjits Excel VBA 5 August 1st, 2006 03:04 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.