Loading an Excel file saved in VB

The following is how I'm opening an Excel file in Form_Load when VB first starts:

Code:

---

    Set xlApp = New Excel.Application
    Set xlBook = xlApp.Workbooks.Open(App.Path & "\test.xls")
    Set xlSheetData = xlBook.Worksheets("input")

---

The VB program changes the cell values in test.xls. If the user wants to save the changes, I have a menu option:

Code:

---

Private Sub mnuSave_Click()
    Dim xlFile As String

    On Error Resume Next
   
    comDialog.Filter = "Excel File (*.xls)  |  *.xls"
    comDialog.FileName = "*.xls"
 
    xlFile = comDialog.FileName
    xlBook.SaveAs xlFile

End Sub

---

My primary question is - the next time the user runs the VB application, how can I modify the Form_Load code so that the saved "xlFile" is loaded instead of the default "test.xls"? Also, is it possible to show the Excel file name in the caption property of a Form?

Thanks

On the form load event use:

Code:

---

dim strLastFile as string
strLastFile = getsetting(app.exename,"Settings","LastFile","None")
Set xlApp = New Excel.Application
if strLastFile = "None" then
    Set xlBook = xlApp.Workbooks.Open(App.Path & "\test.xls")
else
    Set xlBook = xlApp.Workbooks.Open(strLastFile)
end if
Set xlSheetData = xlBook.Worksheets("input")

---

On the save menu use:

Code:

---

Private Sub mnuSave_Click()
    Dim xlFile As String

    On Error Resume Next
   
    comDialog.Filter = "Excel File (*.xls)  |  *.xls"
    comDialog.FileName = "*.xls"
 
    xlFile = comDialog.FileName
    xlBook.SaveAs xlFile
    SaveSetting App.ExeName,"Settings","LastFile",xlFile
End Sub

---

That should do the trick.

Didn't notice the 2nd question - Yes..... on form load add after the original code:

Code:

---

me.caption = me.caption & ": " & xlBook.Path & "\" & xlBook.Name

---

Thanks XQ - both solutions work great.

Stan