opening MS Word and MS Excel from VBA 1

[newprogamer]

Hello,

How do I open MS Word and MS Excel from VBA? I have the path and name of an excel file and a Word file saved in an access database. I have used a data grid to display the file names from the access database. When the user clicks on the row in the datagrid. I would like to open excel or word and then open the file. How do I do this? Please help.

 

[PHV]

In access VBA you may consider the Application.FollowHyperlink method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[newprogamer]

Below is the code I am trying to use. I don't receive an error message but Word doesn't open either.

'*********************************************
Private Sub DataGrid1_RowColChange(LastRow As Variant, ByVal LastCol As Integer)
'Need Code to clear grid
Call OpenQuote
End Sub

'****************

'**** try to open with VBA
'Open Quote

If optWordQuote.Value = True Then
Dim objwordapp As Object 'this is Word
Dim objWordFile As Object 'this is the Quote template -- HingeR4

'Quote name
Dim strfile As String
strfile = DataGrid1.Columns("WordQuote") 'WordQuote is the full path

'creating new file from template
Set objwordapp = CreateObject("Word.Application")
Set objWordFile = objwordapp.documents.Open(strfile)
Else
'open excel spreadsheet
MsgBox "Excel code not written yet."
End If

End Sub
'*******************
PHV, I tried Application.FollowHyperlink but I do not have the .FollowHyperlink method available to me.

Any help is appreciated. Thanks!

 

[PHV]

Try this:
objwordapp.Visible = True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[newprogamer]

Thanks PHV, that solved the problem! Here is the code below. I still need to write code to open the excel file.

'********************
Private Sub DataGrid1_RowColChange(LastRow As Variant, ByVal LastCol As Integer)
' Call ClearDetail 'Clear page
Call OpenQuote
End Sub

Private Sub OpenQuote()

On Error Resume Next

'open using with VBA
If optWordQuote.Value = True Then 'Open Word Quote
Dim objwordapp As Object 'this is Word
Dim objWordFile As Object 'this is the WordQuote
Dim strfile As String 'Quote name
strfile = DataGrid1.Columns("WordQuote") 'WordQuote is the full path

Set objwordapp = CreateObject("Word.Application")
Set objWordFile = objwordapp.documents.Open(strfile) 'open quote
objwordapp.Visible = True

Else
'open excel spreadsheet
MsgBox "Excel code not written yet."
End If

End Sub

 

[PHV]

Do it the same way with Excel.Application and its Workbooks collection.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[newprogamer]

Hi PHV, thanks for your help again. I ended up with

Else
'open excel spreadsheet
Dim objExcelapp As Object 'this is Excel
Dim objExcelfile As Object 'this is the Excel file
strFile = DataGrid1.Columns("Name") 'full path to Excel file

Set objExcelapp = CreateObject("Excel.Application")
Set objExcelfile = objExcelapp.Workbook.Open(strFile) 'open spreadsheet
objExcelapp.Visible = True

End If

'***********************
Since I am running a VBA program with spreadsheets running the background, the excel spreadsheet will not open. It will open excel with a blank spreadsheet and put the file name in the list of the most opened files (under the file menu item). However, I can close the VBA program and click file - then the file name to open the spreadsheet.

Is there a way to make the desired spreadsheet open with the VBA program running?