Dear Excel expert,
I used a template to create a workbook and make it visible. If another workbook was opened when run the VB program, The new workbook is not show up. I close exit the program and go to the folder that stored the new workbook to open it. It is empty.
Could you teach me how to make it works when another workbook was opened.
thank you very much.
The code example is as follow.
Private Sub CreateButton_Click()
Dim Report As Object .
Dim ReportName As String
Dim strExcelFileName As String
Dim strReportName As String
ReportName = App.Path & "template.XLT"
Set Report = GetObject(ReportName)
strExcelFileName = Application.GetSaveAsFilename(App.Path & "" & "test .xls", fileFilter:="Excel Files (*.xls), *.xls")
Report.SaveAs FileName:=strExcelFileName
strReportName = strExcelFileName
Set Report = GetObject(strReportName)
Report.Application.Visible = True
Report.Parent.Windows(1).Visible = True
Report.Worksheets("report1").Select
DoEvents
With Report.Worksheets(1)
.Range("A2").Value = "Last Name"
.Range("A3").Value = "First Name"
End With
Report.Save
End Sub
ssunchen
I used a template to create a workbook and make it visible. If another workbook was opened when run the VB program, The new workbook is not show up. I close exit the program and go to the folder that stored the new workbook to open it. It is empty.
Could you teach me how to make it works when another workbook was opened.
thank you very much.
The code example is as follow.
Private Sub CreateButton_Click()
Dim Report As Object .
Dim ReportName As String
Dim strExcelFileName As String
Dim strReportName As String
ReportName = App.Path & "template.XLT"
Set Report = GetObject(ReportName)
strExcelFileName = Application.GetSaveAsFilename(App.Path & "" & "test .xls", fileFilter:="Excel Files (*.xls), *.xls")
Report.SaveAs FileName:=strExcelFileName
strReportName = strExcelFileName
Set Report = GetObject(strReportName)
Report.Application.Visible = True
Report.Parent.Windows(1).Visible = True
Report.Worksheets("report1").Select
DoEvents
With Report.Worksheets(1)
.Range("A2").Value = "Last Name"
.Range("A3").Value = "First Name"
End With
Report.Save
End Sub
ssunchen