XLSheetToArray

Hints, tips and tricks for newbies

Moderators: JRL, Dorian (MJT support)

Post Reply
nodochau
Pro Scripter
Posts: 56
Joined: Wed Jan 16, 2019 12:59 pm

XLSheetToArray

Post by nodochau » Wed Feb 12, 2020 6:52 pm

Hello All,
I have an Excel spreadsheet that has Customer name and Purchase Orders info. I am using XLSheetToArray to find the customer name and return the Order. The customer name is Mani but my program does not find it.
Do I miss something?
Here is my code:

Code: Select all

XLSheetToArray>C:\BH\service-1.xls,Sheet1,xldata
let>k=1
let>customer_name=Mani
While>k>=1
Let>t=xldata_%k%_2
if>t=customer_name
mdl>found
exit
Endif
mdl>t
Add>k,1
EndWhile

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

Re: XLSheetToArray

Post by Grovkillen » Wed Feb 12, 2020 8:33 pm

Have you stepped through your code?
Let>ME=%Script%

Running: MS14.5.4

nodochau
Pro Scripter
Posts: 56
Joined: Wed Jan 16, 2019 12:59 pm

Re: XLSheetToArray

Post by nodochau » Thu Feb 13, 2020 11:37 am

Yes I did. Did a debug line by line and it did not stop at the customer Mani. Any spaces, strings..related??

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

Re: XLSheetToArray

Post by Dorian (MJT support) » Thu Feb 13, 2020 2:19 pm

If you have Macro Scheduler samples installed, try your code on example.xls. I tweaked the variables and what field it looks for, but this demonstrates your code is good.

Code: Select all

XLSheetToArray>%script_dir%\samples\example.xls,Sheet1,xldata
let>k=1
let>customer_name=Entertainment
While>k>=1
Let>t=xldata_%k%_5
if>t=customer_name
mdl>found
exit
Endif
mdl>t
Add>k,1
EndWhile
Yes, we have a Custom Scripting Service. Message me or go here

nodochau
Pro Scripter
Posts: 56
Joined: Wed Jan 16, 2019 12:59 pm

Re: XLSheetToArray

Post by nodochau » Thu Feb 13, 2020 3:12 pm

It seems like there are some problems with my spreadsheet. I don't know why.
Just created a new one and code worked find. The file that I get the issue with was a xlsx file and I saved it as xls to use the command. Will it be a problem?? Any ideas? Or people at my work are using some unknown format ... :roll:
Thanks

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

Re: XLSheetToArray

Post by Grovkillen » Thu Feb 13, 2020 3:18 pm

If you read the manual you'll see that the XL commands are XLS compatible only.
Let>ME=%Script%

Running: MS14.5.4

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

Re: XLSheetToArray

Post by Grovkillen » Thu Feb 13, 2020 3:19 pm

What I do is to first convert them using VBS and then open the temp XLS file using generic commands.
Let>ME=%Script%

Running: MS14.5.4

nodochau
Pro Scripter
Posts: 56
Joined: Wed Jan 16, 2019 12:59 pm

Re: XLSheetToArray

Post by nodochau » Thu Feb 13, 2020 3:25 pm

Just wanted to give you an update. I copy some data from the Sheet that I get the issue with and paste them into the new one so that I have new (created with brand new excel sheet) and old (copied from the issue sheet) data in my new sheet. Code works OK with new data and does not work with old data.

nodochau
Pro Scripter
Posts: 56
Joined: Wed Jan 16, 2019 12:59 pm

Re: XLSheetToArray

Post by nodochau » Thu Feb 13, 2020 3:26 pm

Grovkillen wrote:
Thu Feb 13, 2020 3:18 pm
If you read the manual you'll see that the XL commands are XLS compatible only.
That is the reason why I save it as a xls format.

User avatar
Marcus Tettmar
Site Admin
Posts: 7122
Joined: Thu Sep 19, 2002 3:00 pm
Location: Dorset, UK
Contact:

Re: XLSheetToArray

Post by Marcus Tettmar » Thu Feb 13, 2020 3:41 pm

Grovkillen wrote:
Thu Feb 13, 2020 3:18 pm
If you read the manual you'll see that the XL commands are XLS compatible only.
This is not true. It does NOT say this in the manual.

XLSheetToArray works only with xls files. XLS files are not archives like xlsx.

**ALL** the other XL functions work on ANY Excel file that Excel can open as they work by automating Excel via ADO.
Marcus Tettmar
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar

Did you know we are now offering affordable monthly subscriptions for Macro Scheduler Standard?

User avatar
Marcus Tettmar
Site Admin
Posts: 7122
Joined: Thu Sep 19, 2002 3:00 pm
Location: Dorset, UK
Contact:

Re: XLSheetToArray

Post by Marcus Tettmar » Thu Feb 13, 2020 3:42 pm

Grovkillen wrote:
Thu Feb 13, 2020 3:18 pm
If you read the manual you'll see that the XL commands are XLS compatible only.
The OP's post quotes an xls file.
Marcus Tettmar
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar

Did you know we are now offering affordable monthly subscriptions for Macro Scheduler Standard?

User avatar
Marcus Tettmar
Site Admin
Posts: 7122
Joined: Thu Sep 19, 2002 3:00 pm
Location: Dorset, UK
Contact:

Re: XLSheetToArray

Post by Marcus Tettmar » Thu Feb 13, 2020 3:44 pm

nodochau wrote:
Thu Feb 13, 2020 3:25 pm
Just wanted to give you an update. I copy some data from the Sheet that I get the issue with and paste them into the new one so that I have new (created with brand new excel sheet) and old (copied from the issue sheet) data in my new sheet. Code works OK with new data and does not work with old data.
There must be something in that file that XLSSheetToArray doesn't like. You might be better off using XLOpen and XLGetCell in a loop. This will work with any Excel file as long as you have Excel installed on the same PC as the macro.

https://manuals.mjtnet.com/articles/#!m ... /xlgetcell
Marcus Tettmar
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar

Did you know we are now offering affordable monthly subscriptions for Macro Scheduler Standard?

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

Re: XLSheetToArray

Post by Grovkillen » Thu Feb 13, 2020 4:05 pm

Marcus Tettmar wrote:
Thu Feb 13, 2020 3:41 pm
Grovkillen wrote:
Thu Feb 13, 2020 3:18 pm
If you read the manual you'll see that the XL commands are XLS compatible only.
This is not true. It does NOT say this in the manual.

XLSheetToArray works only with xls files. XLS files are not archives like xlsx.

**ALL** the other XL functions work on ANY Excel file that Excel can open as they work by automating Excel via ADO.
Noted
Let>ME=%Script%

Running: MS14.5.4

nodochau
Pro Scripter
Posts: 56
Joined: Wed Jan 16, 2019 12:59 pm

Re: XLSheetToArray

Post by nodochau » Thu Feb 13, 2020 8:08 pm

Marcus Tettmar wrote:
Thu Feb 13, 2020 3:44 pm
nodochau wrote:
Thu Feb 13, 2020 3:25 pm
Just wanted to give you an update. I copy some data from the Sheet that I get the issue with and paste them into the new one so that I have new (created with brand new excel sheet) and old (copied from the issue sheet) data in my new sheet. Code works OK with new data and does not work with old data.
There must be something in that file that XLSSheetToArray doesn't like. You might be better off using XLOpen and XLGetCell in a loop. This will work with any Excel file as long as you have Excel installed on the same PC as the macro.

https://manuals.mjtnet.com/articles/#!m ... /xlgetcell
Thanks Marcus. I do know that I can use XLOpen and loop through the list to find the customer. I just wanted to see does XLSheetToArray command perform the task quicker or not cause we have thousands customers in the list.

Post Reply