Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Save or open file as YYYYMMDD in Macros, how to record it | Excel Discussion (Misc queries) | |||
How can I save or open file as YYYYMMDD in Macros | Excel Discussion (Misc queries) | |||
In Excel - Use Windows Explorer instead of File Open to open file | Excel Discussion (Misc queries) | |||
How to stop file open macro prompt after deleting all macros? | Excel Worksheet Functions | |||
save excel file from a table delimited file (.txt) using macros | New Users to Excel |