here is my situation.  i have a group of users and each user has an
HTML report.  i ULTIMATELY want to have each user's report on a
worksheet within ONE excel workbook.

so this is what i do.  i loop through the group of users and use an
"AspHttp" object to call another ASP script to generate a report for
each user.  this object returns the HTML report in string format which
i then save to a file on the server.  THEN i create an excel workbook
and open that file, and then save the excel file on the server and
delete the HTML file.  so i wind up with a bunch of excel workbooks
(one for each user).

i originally thought i could create an excel workbook, and then open
the HTML file on each worksheet.  but alas, that didn't work because
everytime i opened an HTML file a new workbook would be created...so i
resorted to having multiple workbooks thinking i could somehow combine
them into one workbook.  BUT i haven't been able to figure it out.
here is how my code works so far.

<%
Dim objFileObj
Set objFileObj = Server.CreateObject("Scripting.FileSystemObject")

Dim objFile
Set XLApp = CreateObject("Excel.Application")
XLApp.Visible = False

Do While Not rsUsers.EOF

  ' figure out filenames
  strFileNameHTM = rsUsers("cn") & ".htm"
  strFullPathHTM = Server.MapPath("/<foobar>/" & strFileNameHTM)
  strFileNameXLS = rsUsers("cn") & ".xls"
  strFullPathXLS = Server.MapPath("/<foobar>/" & strFileNameXLS)

  ' get the report
  Set HttpObj = Server.CreateObject("AspHTTP.Conn")
  HttpObj.Url = "http://<server>/<url>/attributereport.asp"
  HttpObj.FollowRedirects = true
  HttpObj.RequestMethod = "GET"
  HttpObj.UserAgent = "Mozilla/2.0 (compatible; MSIE 3.0B; Windows
NT)"
  HttpObj.TimeOut = 900
  strResult = HttpObj.GetURL
  Set HttpObj = Nothing

  Set objFile = objFileObj.OpenTextFile( strFullPathHTM, 2, True,
False )
  If Err = 0 Then
    ' write report to html file
    objFile.Write strResult

    ' open html file inside excel & save as excel file
    XLApp.Workbooks.Add
    XLApp.Workbooks.Open strFullPathHTM
    XLApp.ActiveWorkbook.SaveAs strFullPathXLS,43

    objFile.close
    objFileObj.DeleteFile strFullPathHTM, true
  Else
    objFile.close
    Response.Write "Error while creating report"
  End If

  rsUsers.MoveNext
Loop    

XLApp.Quit
Set XLApp = Nothing

Set objFile = Nothing
Set objFileObj = Nothing
%>

if anyone has any suggestions as to how to combine all of these
workbooks into one workbook with many worksheets using VBScript, i
would really appreciate it.  it's going to be like 200 workbooks!  or
if anyone has an answer to my original idea of opening each html file
into worksheets in one workbook that would be even better!

thanks so much for taking the time to even read this far!  =)

susie

' open html file inside excel
XLApp.Workbooks.Add
XLApp.Workbooks.Open strFullPathHTM

' copy first sheet to clipboard and discard workbook
XLApp.ActiveWorkbook.Sheets(1).Copy
XLApp.ActiveWorkbook.Close

' add new sheet to combined workbook and paste from clipboard
XLApp.Workbooks("CombinedWorkbook").Sheets.Add
XLApp.Workbooks("CombinedWorkbook").Sheets(1).Paste

thanks so much for your reply.  it helped me move in the right
direction!  i ran into a few problems with referencing the correct
workbook and encountering very odd errors when looping..but i think i
finally got it.

Set XLApp = CreateObject("Excel.Application")
XLApp.Visible = False

' set because when i would .Close a workbook
' it would stall excel waiting for an answer to the save dialog box
XLApp.DisplayAlerts = False

' #1 workbook in workbooks collection
XLApp.Workbooks.Add

' set counter for referencing worksheet #
count = 1

' loop through users
Do While Not rsUsers.EOF

    ' #2 workbook in workbooks collection
    XLApp.Workbooks.Add

    ' open html file
    XLApp.Workbooks.Open strFullPathHTM

    ' copy first worksheet in #2 workbook to #1 workbook
    XLApp.ActiveWorkbook.Worksheets(1).Copy
XLApp.Workbooks(1).Worksheets(count)

    ' close #2 workbook
    XLApp.Workbooks(2).Close

    count = count + 1
    rsUsers.MoveNext
Loop

' delete initial 3 empty worksheets
XLApp.ActiveWorkbook.Worksheets("Sheet1").Delete
XLApp.ActiveWorkbook.Worksheets("Sheet2").Delete
XLApp.ActiveWorkbook.Worksheets("Sheet3").Delete
' save workbook #1
XLApp.ActiveWorkbook.SaveAs strFullPathXLS,43
XLApp.Quit
Set XLApp = Nothing

seems to work ok so far....i haven't tested it with the main group of
200 some odd users.  keep your fingers crossed.

thanks again,
susie

I didn't find that .Copy syntax that you used, but I knew it must be in
there somewhere. :)