xlGetSheetDims returns wrong number of rows

Technical support and scripting issues

Moderators: Dorian (MJT support), JRL

Post Reply
Tourless
Junior Coder
Posts: 39
Joined: Wed Jun 14, 2017 1:53 am
Location: NY

xlGetSheetDims returns wrong number of rows

Post by Tourless » Wed Oct 09, 2019 8:28 pm

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.

User avatar
Grovkillen
Automation Wizard
Posts: 360
Joined: Fri Aug 10, 2012 2:38 pm
Location: Hudiksvall, Sweden
Contact:

Re: xlGetSheetDims returns wrong number of rows

Post by Grovkillen » Thu Oct 10, 2019 5:56 am

Are you sure you haven't clicked or marked a cell some rows below? What you see isn't always what Excel sees...
Let>ME=%Script%

Running: MS14.5.4

Tourless
Junior Coder
Posts: 39
Joined: Wed Jun 14, 2017 1:53 am
Location: NY

Re: xlGetSheetDims returns wrong number of rows

Post by Tourless » Thu Oct 10, 2019 12:57 pm

Grovkillen wrote:
Thu Oct 10, 2019 5:56 am
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.

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 478
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: xlGetSheetDims returns wrong number of rows

Post by Dorian (MJT support) » Thu Oct 10, 2019 3:36 pm

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.
Always accepting custom script commissions. Message me or go here

Tourless
Junior Coder
Posts: 39
Joined: Wed Jun 14, 2017 1:53 am
Location: NY

Re: xlGetSheetDims returns wrong number of rows

Post by Tourless » Fri Oct 11, 2019 1:53 pm

Dorian (MJT support) wrote:
Thu Oct 10, 2019 3:36 pm
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!

Post Reply