[M$ Office Object Model / VBScript] Excel Object Model Help? - Tilted Forum Project Discussion Community

cartmen34 · 08-06-2005, 01:40 PM

Hello all,

I've got an automation program which uses an excel sheet to store and access data. Because of this, I've developed several functions which mine the spreadsheet(s) for data using WCH (Windows Script Host) and the M$ Office Object Model for Scripting. To get the data from the sheet, I'm using lines "a something a likea' dis":

Code:

Set mySheet = ExcelObj.Sheet.Item("MySheetName")
Set myRange = mySheet.Range("A1").CurrentRegion

rowCount = myRange.Rows.Count
colCount = myRange.Columns.Count

(You'll have to excuse me if the terminology is slightly askew, as I'm teaching myself this stuff as problems crop up)

Now here is my problem. I call the functions and access the data. No problems. Row and column counts are as expected. Then somebody (sometimes me) accesses the data by opening the file in Excel 2003. They edit the existing data, (read: they DO NOT add new rows or columns to the sheet), save their changes, and close the sheet.
Then I run my function again. All hell breaks loose. Suddenly my row count and column count get the currentregions of ALL the sheets, and sometimes they even get the counts of all the sheets in the spreadsheet!! It's different results everytime an edit is made. It's all whacky.

This is happening even if we do something as small as edit existing text values in a single cell.

The only fix seems to be to open the spreadsheet back up, and in every sheet select every empty row and every empty column, then do an Edit/Clear/All. However, sometimes this works, and sometimes it doesn't. The worst fix is actually selecting only the data we need and copy/pasting it to a brand new excel file.

Anybody? Any ideas? I've already searched MSDN and found nothing like this, hoever, my search skills aren't the best so.....

Thanks for your help peoples!!

Moskie · 08-07-2005, 04:07 AM

One thing I've come across is that using ActiveSheet.UsedRange.(Rows|Columns).Count is the best way to get the total number of rows/columns. Not sure if that's the issue here, but give it a shot.

08-06-2005, 01:40 PM	#1 (permalink)
cartmen34 Psycho Location: the hills of aquafina.	[M$ Office Object Model / VBScript] Excel Object Model Help? Hello all, I've got an automation program which uses an excel sheet to store and access data. Because of this, I've developed several functions which mine the spreadsheet(s) for data using WCH (Windows Script Host) and the M$ Office Object Model for Scripting. To get the data from the sheet, I'm using lines "a something a likea' dis": Code: Set mySheet = ExcelObj.Sheet.Item("MySheetName") Set myRange = mySheet.Range("A1").CurrentRegion rowCount = myRange.Rows.Count colCount = myRange.Columns.Count (You'll have to excuse me if the terminology is slightly askew, as I'm teaching myself this stuff as problems crop up) Now here is my problem. I call the functions and access the data. No problems. Row and column counts are as expected. Then somebody (sometimes me) accesses the data by opening the file in Excel 2003. They edit the existing data, (read: they DO NOT add new rows or columns to the sheet), save their changes, and close the sheet. Then I run my function again. All hell breaks loose. Suddenly my row count and column count get the currentregions of ALL the sheets, and sometimes they even get the counts of all the sheets in the spreadsheet!! It's different results everytime an edit is made. It's all whacky. This is happening even if we do something as small as edit existing text values in a single cell. The only fix seems to be to open the spreadsheet back up, and in every sheet select every empty row and every empty column, then do an Edit/Clear/All. However, sometimes this works, and sometimes it doesn't. The worst fix is actually selecting only the data we need and copy/pasting it to a brand new excel file. Anybody? Any ideas? I've already searched MSDN and found nothing like this, hoever, my search skills aren't the best so..... Thanks for your help peoples!! __________________ "The problem with quick and dirty, as some people have said, is that the dirty remains long after the quick has been forgotten" - Steve McConnell

08-07-2005, 04:07 AM	#2 (permalink)
Moskie Pure Chewing Satisfaction Location: can i use bbcode [i]here[/i]?	One thing I've come across is that using ActiveSheet.UsedRange.(Rows\|Columns).Count is the best way to get the total number of rows/columns. Not sure if that's the issue here, but give it a shot. __________________ Greetings and salutations. Last edited by Moskie; 08-07-2005 at 04:09 AM..