Open file with macros

[David T]

Hello all-

I have a macro that opens a file on my network when I click on button. I
want this macro to open a file that is in cell(14,1) no matter what the
file's name is changed to. I've attached my current macro below. If I
change the actually file name in VBA code, then it works fine. But i want
to be able to change the file name in the Excel worksheet instead.

Private Sub CmdWireSheet_Click()

Dim objExcel As Object
Dim SchedWkbk As Workbook

On Error Resume Next
Set SchedWkbk = Nothing

Set objExcel = CreateObject("Excel.Application")

Set SchedWkbk = objExcel.Application.Workbooks.Open _
("O:\SEM\Common\Accounting\& .cells(14,1).value")
objExcel.Visible = True
On Error Resume Next

If SchedWkbk Is Nothing Then
MsgBox prompt:="Cannot find file. Please open file manually", _
Buttons:=vbOKOnly + vbQuestion

End If

End Sub

 

[Gary''s Student]

Considering using a hyperlink rather than a button. The hyperlink will open
the file if not already openned and branch if already openned:

=HYPERLINK("file:///O:\SEM\Common\Accounting\" & A14,"file on server")

 

[David T]

Gary-

I prefer not to use a hyperlink because it will open the file within the
same workbook. This macro will open the file in entirely different workbook.
Anyhow, after playing around with the code, I figured out how to fix the
code. All I had to do was move the quotations around. Thanks for your
response.

Private Sub CmdWireSheet_Click()

Dim objExcel As Object
Dim SchedWkbk As Workbook

On Error Resume Next
Set SchedWkbk = Nothing

Set objExcel = CreateObject("Excel.Application")

Set SchedWkbk = objExcel.Application.Workbooks.Open _
("O:\SEM\Common\Accounting\" & Cells(5, 9).Value)
objExcel.Visible = True
On Error Resume Next

If SchedWkbk Is Nothing Then
MsgBox prompt:="Please check cell I5 to make sure the correct file
path is entered.", _
Buttons:=vbOKOnly + vbQuestion

End If

End Sub