Ramon,

Use Application.DisplayAlerts=false to turn the messages off

And Application.DisplayAlerts=true to turn them back on.

In the code I provided, it would be xlApp.DisplayAlerts=False

John Ruff - The Eternal Optimist :)


-----Original Message-----
From:	RPAmog@s... [mailto:RPAmog@s...]
Sent:	Thursday, May 31, 2001 1:00 AM
To:	professional vb
Subject:	[pro_vb] RE: How to copy data in multiple excel worksheets?

Worked great!  Thanks!

I do have one more problem though.  During the process of deleting, I am
prompted by the following message:

"The selected sheet(s) will be permanently deleted.
	To delete the selected sheets, click OK.
	To cancel the deletion, click Cancel."

And I have to click OK twice.  How do I prevent the prompts from appearing?

TIA

Ramon

-----Original Message-----
From: John Ruff [mailto:papparuff@c...]
Sent: Tuesday, May 29, 2001 5:55 PM
To: professional vb
Subject: [pro_vb] RE: How to copy data in multiple excel worksheets?


To answer your second question first.  Use the following code to delete all
worksheets except one. (At least one worksheet has to be present with a
workbook.

Dim I as Integer

For i = xlBook.Worksheets.Count To 2 Step -1
    xlBook.Sheets(i).Delete
Next i

Question One.

The best way to add data to more than one worksheet is to use a recordset
and then loop thru the recordset like this.

Dim rstTemp as Recordset
Dim fOK as Boolean

Set rstTemp=Currentdb.Openrecordset("tbl_Parts")

Do While Not rstTemp.EOF
	' Loop through each Worksheet and see if the name starts with Sheet.
	' If it does, replace it with the name I specify, in this case the
value of
strPartNo
For i = 1 To xlBook.Worksheets.Count
If xlBook.Sheets(i).Name = "Sheet" & i Then
 xlBook.Sheets(i).Name = strPartNo
Set xlSheet = xlBook.ActiveSheet
fOK = True
Exit For
 Else
' If there are not worksheets that start with "Sheet", then...
fOK = False
Exit For
End If
Next I

' If there are no worksheets that star with Sheet, then add a new one.
If Not fOK Then
xlBook.Sheets.Add
xlBook.Sheets(1).Name = strPartNo
Set xlSheet = xlBook.ActiveSheet
End If

' Make the current sheet visible
xlSheet.Visible = True


    rstTemp.MoveNext
Loop

I hope this helps.

John Ruff - The Eternal Optimist :)
 -----Original Message-----
From: 	RPAmog@s... [mailto:RPAmog@s...]
Sent:	Monday, May 28, 2001 7:34 PM
To:	professional vb
Subject:	[pro_vb] How to copy data in multiple excel worksheets?

1. I'm writing an app that can print reports in MS 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--->