Opening an excel worksheet

[ScottInTexas]

I want to open an excel worksheet and then collect values from cells using row and col references.

I can set it up where the rows and column resemble a recordset and used ADODB, but there are other reasons where this would NOT be the best approach.

How do I create an excel object?

[Spudhead]

Ouch. I think we might need more info about what you're trying to do with this data. You can certainly open a ADODB connection to your spreadsheet, and treat it as such. That, to me, would seem to be the most straightforward, elagant and powerful way of doing it. The only other way that springs to mind is opening the file via the FileSystemObject - which doesn't really bring many benefits in terms of manipulating the data.

That wasn't very helpful, was it? Sorry:rolleyes:

[Roy Sinclair]

I think he wants to open it as an object and then manipulate it from there. I suspect the information he needs is inside the VBA documentation for Excel.

[ScottInTexas]

The reason I have to do it this way is because the Excel file is not laid out in a nice "Recordset" format. There is data that runs in columns and some values that run in rows. Some of these values must be calcualted with a constant that can be found at an individual cell.

I have no control over the format. I just gotta read the data and produce the desired chart on the web page. And it is not an excel chart. It is a chart applet to which I must feed numbers.

If I can open the desired worksheet then I would start a loop like this


Function GetDataForLine()
Dim row
Dim col
Dim dataItem
Dim linedata
row=23
col=1
linedata="Line1="
while worksheet.cells(row, col).value<>""
dataItem=worksheet.cells(row, col).value * worksheet.cells(1,3).value
linedata=linedata & dataItem
row=row+1
wend

linedata=linedata & ";"

GetDataForLine=linedata
end function



That is essentially the loop. So I need to open the excel file and create a worksheet object so I can't use the standard "Select * from Range" format to create a recordset.

Thanks

[Roelf]

Set xlApp = CreateObject("Excel.Application")
xlApp.Application.Visible = false
Set xlDoc = xlApp.Open("yourfile.xls")

Should instantiate an excel object. Beware, the user which is used for the internet account (prob. IUSR_Servername) must have started excel once, otherwise the msi will try to set the user settings for excel for this user.
I have done it like:
create a dummy user, log in to W2K once with this user account, start excel, then change the iis settings so that this new user will be used as the anonymous internet user account

[ScottInTexas]

The set XlApp=CreateObject("Excel.Application") throws an error. Keep in mind that this is an ASP program. I have also tried server.createobject("Excel.Application") .

I appreciate your response. I hope you get a chance to see this also.

[Roelf]

have you tried doing the user thingy?

[ScottInTexas]

I don't have a clue how to change the iis settings or how to log on an anonymous user. But as I understand what you said in your previous post I have to have IUSR_Servername log in and change the settings so that an anonymous user (anyone) can log in later and open the file. Doesn't this also jeopardize security?

As it is now if I set up an excel file with proper rows of data and name the range I can open the file (in the same location on the virtual directory as the file in question) and read the rows using the headings at the top of the columns as field names.

This is great except that I cannot change the original file format to suit my needs, I just have to use what's there.

I hope this is making sense because I don't want to find out 3 days from now that there was an easy answer if someone just knew what I was doing. I can do this in VBA from Outlook or Access but whenever I get into running ASP's and this miserable vbscript I loose control and things just don't flow right! It would also be a big help if I had a decent editor for ASPs but Visual Interdev just doesn't like to create individual files, it only seems to want to create entire web sites. Maybe there's a learning curve I don't want to deal with.

Thanks a lot for your response.