Open file with macros
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 |
Open file with macros
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") -- Gary''s Student - gsnu200759 "David T" wrote: 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 |
Open file with macros
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 "Gary''s Student" wrote: 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") -- Gary''s Student - gsnu200759 "David T" wrote: 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 |
All times are GMT +1. The time now is 06:02 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com