Making a button to open a work book

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Here is what I have. I have a work book with one sheet in it, I call it IQS_2.xls. I have another workbook that links to it and grabs info from it and enters it into the designated cells, I call this one EMQS.xls. Is it possible to have a button on the IQS_2.xls sheet to automatically open EMQS.xls and save it with the prefix Quote_LLXXXXX (the XXXXX would be changed to a number; I would also like to have it save in a specified directory. Any help suggestions would be greatly appreciated.

Thanks,

Darrell
 
One way:

Attach this macro to the button:

Public Sub Button1_Click()
Const PATHIQS As String = "<your path to IQS_2>"
Const PATHDEST As String = "<path to 'specified directory'>"
Dim sOpenPath As String
Dim sSavePath As String
Dim sXXXXX As String

sOpenPath = PATHIQS & Application.PathSeparator & "IQS_2.xls"
sXXXXX = Format(1, "00000") 'Change to suit
sSavePath = PATHDEST & Application.PathSeparator & _
"Quote_LL" & sXXXXX & ".xls"
If Dir(sOpenPath) <> "" Then
Workbooks.Open sOpenPath
With ActiveWorkbook
.SaveAs sSavePath
.Close False
End With
End If
End Sub
 
Darrell,

Off the top

Workbooks.Open Filename:="EMQS.xls "
' do something
Activeworkbook.SaveAs Filename:="C:\MyDir\MySubDir\Quote_LL1234" &
Activeworkbook.Name

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Darrell said:
Here is what I have. I have a work book with one sheet in it, I call it
Click to expand...
IQS_2.xls. I have another workbook that links to it and grabs info from it
and enters it into the designated cells, I call this one EMQS.xls. Is it
possible to have a button on the IQS_2.xls sheet to automatically open
EMQS.xls and save it with the prefix Quote_LLXXXXX (the XXXXX would be
changed to a number; I would also like to have it save in a specified
directory. Any help suggestions would be greatly appreciated.
 
Back
Top