General information
Category:
Coding, syntax & commands
Title:
How to save multiple worksheets in excel object?
1. I'm writing an app that can print reports in excel. I've been successful in adding worksheets and saving the workbook. The problem is, the data I wish to write is only stored in one worksheet instead of on multiple worksheets. How do I remedy this? Can anyone give me a hand in modifying the code below?
2. Is there a way to check how many worksheets have been created? I noticed that when the workbook object is created it already has three worksheets. Is there a way for me to create the workbook with only one worksheet? Thanks!
<---Code begins--->
Private Sub Command1_Click()
Dim xlShell
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
nWorkSheetCtr = 0
For i = 1 To 4
nWorkSheetCtr = nWorkSheetCtr + 1
xlBook.Worksheets.Add
Set xlSheet = xlBook.Worksheets(nWorkSheetCtr)
xlSheet.Cells(1, 1) = "TEST REPORT SYSTEM"
xlSheet.Cells(2, 1) = "SAMPLE REPORT"
xlSheet.Cells(3, 1) = "Run Date/Time: " & strDateTime
xlSheet.Cells(6, 1) = "NAME" & Trim(Str(i))
xlSheet.Cells(6, 2) = "POSITION"
Next i
cFileName = "c:\temp\test.xls"
xlBook.SaveAs cFileName
xlBook.Close
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
xlShell = Shell("C:\Program Files\Microsoft Office\Office\Excel.exe " + cFileName, vbNormalFocus)
End Sub
<---Code Ends--->
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only