Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Open csv file from Word VBA (VBA/Word/2003)

    I apologize up front; I received instructions on how to achieve something like this several years ago, but am unable to find it in any searches.

    I am creating a .csv file with a Word macro. What I need help with is code to open the newly created .csv file in Excel. At the time this code is executed Excel may be either running or not.

    Any help will be greatly appreciated.

    T.I.A.
    Regards
    Don

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Open csv file from Word VBA (VBA/Word/2003)

    If you simply want to launch the file in Excel you can use Shell. If you want to have programmatic access to the file, you can use GetObject/CreateObject to get an object reference to (and, if necessary, start) Excel, and then use Excel's own methods to open the file. Does any of that sound familiar?

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open csv file from Word VBA (VBA/Word/2003)

    Here is an example. It uses late binding, so it is not necessary to set a reference to the Excel object library, making your code independent of the version of Excel installed by the client.

    Place this procedure in a standard module:
    <code>
    Sub OpenCSV(FileName As String)
    Dim xlApp As Object
    Dim xlWbk As Object
    Dim blnStartExcel As Boolean
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If xlApp Is Nothing Then
    Set xlApp = CreateObject("Excel.Application")
    If xlApp Is Nothing Then
    MsgBox "Cannot open Excel.", vbCritical
    Exit Sub
    End If
    blnStartExcel = True
    End If
    On Error GoTo ErrHandler
    Set xlWbk = xlApp.Workbooks.Open(FileName)
    ' Optional - make Excel visible
    xlApp.Visible = True
    ExitHandler:
    Set xlWbk = Nothing
    Set xlApp = Nothing
    Exit Sub
    ErrHandler:
    MsgBox Err.Description
    If blnStartExcel And Not xlApp Is Nothing Then
    xlApp.Quit
    End If
    Resume ExitHandler
    End Sub
    </code>
    Use it like this in your own code:
    <code>
    Call OpenCSV("C:MyFolderMyFile.csv")</code>

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Open csv file from Word VBA (VBA/Word/2003)

    Thank you for the speedy response Jefferson, but I'm afraid that you've overestimated my expertise. Or perhaps you've underestimated the extent of my forgetter.
    Regards
    Don

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Open csv file from Word VBA (VBA/Word/2003)

    Hans
    Thank you for the beautifully structured code. You've anticipated my every need. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Regards
    Don

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Open csv file from Word VBA (VBA/Word/2003)

    Hello Hans
    I have adapted your code slightly (as shown below), and managed to confuse myself in the bargain. <pre>Sub OpenCSV(filename As String)
    Dim xlApp As Object
    Dim xlWbk As Object
    Dim blnStartExcel As Boolean
    10 On Error Resume Next
    20 Set xlApp = GetObject(, "Excel.Application")
    30 If xlApp Is Nothing Then
    40 Set xlApp = CreateObject("Excel.Application")
    50 If xlApp Is Nothing Then
    60 MsgBox "Cannot open Excel.", vbCritical
    70 Exit Sub
    80 End If
    90 blnStartExcel = True
    100 End If
    110 On Error GoTo ErrHandler
    120 Set xlWbk = xlApp.workbooks.Open(filename)
    ' Optional - make Excel visible
    130 With xlApp
    140 .Visible = True
    150 .WindowState = -4137
    160 .ActiveWindow.WindowState = 2
    170 End With
    ExitHandler:text</pre>


    I found the constant for line 150 here, but was unable to find the constant for line 160. I arrived at "2" by trial and error.
    Can you advise me on where to find all of the constants? or did I miss something on the MSDN page?

    T.I.A.
    Regards
    Don

  7. #7
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Open csv file from Word VBA (VBA/Word/2003)

    If you view the Object Browser in Excel VBA, and search for WindowState, then it shows the following possible values

    xlMaximized = -4137 (&HFFFFEFD7)
    xlMinimized = -4140 (&HFFFFEFD4)
    xlNormal = -4143 (&HFFFFEFD1)

    These same values are on the web page you referenced, under xlWindowState.
    I don't see a value of 2 at all, what is this supposed to do?

    StuartR

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open csv file from Word VBA (VBA/Word/2003)

    The official Excel constants are

    xlNormal = -4143
    xlMinimized = -4140
    xlMaximized = -4137

    You can find these if you look up xlWindowState in the Object Browser (press F2 in the Visual Basic Editor).

    VB/VBA in general uses constants such as

    vbNormalFocus = 1
    vbMinimizedFocus = 2
    vbMaximizedFocus = 3

    You can find these if you look up vbAppWinStyle in the Object Browser (there are a few other values as well).

    Apparently, the WindowState property "understands" both sets of values, although this is not documented.

  9. #9
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Thanks Gents

    Senility must be creeping in, Of course "-4137" works properly at line 160. I must have mistyped the code when It apparently failed to work. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    Regards
    Don

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •