xlGetSheetDims returns wrong number of rows

[Tourless]

Hi Folks,

I'm working on a script that opens an excel file, runs a macro that, among other things, copies one of the sheets to a new workbook, saves that new workbook with in a specific place with a specific name, and closes both Excel files. Then, I open the newly created file and get it's dimensions which are wrong. The data is pretty stagnant and each time i run it I get the same number of rows/columns. My worksheet has 1061 rows and 18 columns but is reported as having 1064 rows and 30 columns. Not sure what the disconnect is here. Here's the code I'm using.

Code: Select all

XLOpen>C:\py\PostBuild.xlsm,1,xlBook
Wait>3
SetFocus>%xlBook%
XLRun>xlBook,Refresh
//XLQuit>xlBook
Wait>2
XLGet>PostLocations.xlsx,xlH
Wait>3
Label>GET SOURCE DIMS
XLGetSheetDims>xlH,Results,rows,cols
MDL>The sheet has %rows% rows and %cols% columns.

[Grovkillen]

Are you sure you haven't clicked or marked a cell some rows below? What you see isn't always what Excel sees...

[Tourless]

Are you sure you haven't clicked or marked a cell some rows below? What you see isn't always what Excel sees...

Yeah, I'm pretty sure. I guess it's time to take half a step back and work with a subset of my data. I'll let you know what if anything I find. After all , the answer has to be somewhere.

[Dorian (MJT support)]

I think Grovkillen is on the right track. I wouldn't mind betting if you were to copy and paste those 1061x18 into a new sheet it'll find the correct dimensions.

[Tourless]

I think Grovkillen is on the right track. I wouldn't mind betting if you were to copy and paste those 1061x18 into a new sheet it'll find the correct dimensions.

Yep, new sheet! I dumbed down my query to 29 rows of data. In my master spreadsheet I remove then add a new sheet to perform my manipulations and my dims are spot on. Not sure what got added in my prior attempt which deleted all data from the manipulations sheet but I'm not going to worry about it now.

Thanks guys!