VERY Green to QM

[purelife]

Hello everyone,
I have recently downloaded QM, and I am having a very hard time with it. The language it is in is so odd to a newbie, not really similar to anything I have used before.

Here is my current situation. I have a folder with say 40 different excel files in it. I would like to know if it is possible for QM to systematically open each Excel File, do a bit of manipulation to the file, and then dump the data into an Access database...

Is this possible...if so any guidance to where to start would be greatly appreciated.

Thanks, -Pure

[Gintaras]

Possible.

How will you manipulate the files? By sending keys, text? Can do it faster using other functions.

To get path of each excel file, use code like this (created by the 'Enumerate files' dialog):
Macro Macro1195

Code:

Copy      Help

Dir d
foreach(d "$Documents$\*.xls" FE_Dir)
,str sPath=d.FileName(1)
,out sPath
,
,;now what you will do with the file?
,

[purelife]

Wow...I really need to start at square 1 apparently.....that does not make any sense to me at all. And the help isn't much help within the tool either, at least I don' think so.

When I open the file I want to insert 2 columns at the beg. and copy text within the worksheet all the way down those columns. But apparently I have a looonngg road ahead of me before I can even get there.

Thank you for your help!

[purelife]

This isn't even close is it??

The part after 'int w1=sPath" will do what I want it to, if I designate the exact file name....

Code:

Copy      Help

Dir d
foreach(d "C:\Document and Settings\.......\Q4_09" FE_Dir)
    str sPath = d.FileName(1)
    out sPath
run (sPath)
int w1=sPath
'A{}
'icRRDDDDRSD A{}
'oe
5 win("Format Cells" "bosa_sdm_XL9")
'R A{mm}
'Y
act w1
'LR Cc
'DDDDLLL Cv
'RRRUUU Cc
'DDDLL Cv
'LSR Cc
'S{REDL} Cv

[Gintaras]

Should the macro process all files in loop? Or you will run the macro for each file?

If in loop, all code after foreach must be tab-indented. Or comma-indented.

Macro Macro1195

Code:

Copy      Help

;Before you run this macro:
;Excel should not be running.
;Make sure that documents will be maximized, so that document text is displayed in title bar.
;I have Office 2003. If your Office version is other, this macro may not work.

;______________________________________________

;repeat for each excel file
Dir d
foreach(d "$Documents$\*.xls" FE_Dir)
,str sPath=d.FileName(1)
,
,;open, skip or abort?
,str message.from("Open and manipulate this file?[][]" d.FileName)
,sel(mes(message "" "YNC"))
,,case 'C' ret
,,case 'N' continue
,
,;open
,str cl.from("''" sPath "''")
,run "excel" cl
,str excelTitleBarText.from("Microsoft Excel - " d.FileName)
,int w1=wait(30 WA win(excelTitleBarText "XLMAIN"))
,
,mes "Ready?"
,
,'A{}
,'icRRDDDDRSD A{}
,'oe
,5 win("Format Cells" "bosa_sdm_XL9")
,'R A{mm}
,'Y
,act w1
,'LR Cc
,'DDDDLLL Cv
,'RRRUUU Cc
,'DDDLL Cv
,'LSR Cc
,'S{REDL} Cv

--------------------

When posting QM code to the forum, use menu Edit->Other Formats to copy it. Then simply paste here. Don't use the Code button.

[purelife]

Thank you so much!

But when it gets to the part

Code:

Copy      Help

,int w1=wait(10 WA win(excelTitleBarText "XLMAIN"))

I get a message of Error (RT) in Macro2: wait timeout

What is this piece of code doing?

[Gintaras]

What is your Office version?
What text is in Excel title bar when you open the document?
Is Excel version is different, maybe title bar text is formatted differently, or class name is different. Then edit these lines:

str excelTitleBarText.from("Microsoft Excel - " d.FileName)
int w1=wait(30 WA win(excelTitleBarText "XLMAIN"))

Also try to change the second line to:
int w1=wait(30 WA win(excelTitleBarText "" "" 2))

[purelife]

What is your Office version?
What text is in Excel title bar when you open the document?
Is Excel version is different, maybe title bar text is formatted differently, or class name is different. Then edit these lines:

str excelTitleBarText.from("Microsoft Excel - " d.FileName)
int w1=wait(30 WA win(excelTitleBarText "XLMAIN"))

I am using 2003 as well...
When Excel pops open it says 'Microsoft Excel - Radiology Admin'

[Gintaras]

Radiology Admin is file name? Then need to remove .xls from the string.

Macro Macro1195

Code:

Copy      Help

;Before you run this macro:
;Excel should not be running.
;Make sure that documents will be maximized, so that document text is displayed in title bar.
;I have Office 2003. If your Office version is other, this macro may not work.

;______________________________________________

;repeat for each excel file
Dir d
foreach(d "$Documents$\*.xls" FE_Dir)
,str sPath=d.FileName(1)
,
,;open, skip or abort?
,str message.from("Open and manipulate this file?[][]" d.FileName)
,sel(mes(message "" "YNC"))
,,case 'C' ret
,,case 'N' continue
,
,;open
,str cl.from("''" sPath "''")
,run "excel" cl
,
,;wait for document
,str fn.getfilename(d.FileName) ;;remove extension
,str excelTitleBarText.from("Microsoft Excel - " fn)
,int w1=wait(30 WA win(excelTitleBarText "" "" 2))
,
,mes "Ready?"
,
,'CH ;;select first cell
,'A{}
,'icRRDDDDRSD A{}
,'oe
,5 win("Format Cells" "bosa_sdm_XL9")
,'R A{mm}
,'Y
,act w1
,'LR Cc
,'DDDDLLL Cv
,'RRRUUU Cc
,'DDDLL Cv
,'LSR Cc
,'S{REDL} Cv

Today I cannot help more, going to sleep.

[purelife]

It is still timing out

[Gintaras]

I made a mistake in the code. Did not replace d.FileName to fn. Now corrected, should work.

[ken gray]

BTW: you should also visit my blog in the signature. it'll be a great 'start here'.

[TheVig]

Purelife,

I just wanted to take a moment to address your opinion of the QM help file. When I first downloaded QM just over 2 years ago the help file might has well been written in Chinese, but through working through new macros and functions eventually the help file became one of the best help files I have ever seen. Gintaras has meticulously documented the functionality and usability of Quick Macros.

My advice to beginning with QM is to use the record function of QM to record mouse and keyboard actions, then edit those files. Play with them, replace messy parts with loops. Always check the forum for more in depth program control. For example, if you search the forum you'll find quite a lot of ways to directly work with Excel, getting data, setting data and things like that.

Thanks,
Jimmy Vig

[purelife]

Purelife,

I just wanted to take a moment to address your opinion of the QM help file. When I first downloaded QM just over 2 years ago the help file might has well been written in Chinese, but through working through new macros and functions eventually the help file became one of the best help files I have ever seen. Gintaras has meticulously documented the functionality and usability of Quick Macros.

My advice to beginning with QM is to use the record function of QM to record mouse and keyboard actions, then edit those files. Play with them, replace messy parts with loops. Always check the forum for more in depth program control. For example, if you search the forum you'll find quite a lot of ways to directly work with Excel, getting data, setting data and things like that.

Thanks,
Jimmy Vig

Hi Jimmy,

My comments no way meant to degrade the help file...it is just to me it was not useful. Sure once you have an understanding of the tool it would/will be great...but for just starting out as a novice..it is a bit overwhelming. The time put into it is obvious...but it flies over a rooks head.

It would be nice if there was a few example macros created. One thing Microsoft does really well in their dbs is their continuation of example databases to build. Each one builds on the last, and exposes the user to new techniques and functions.

If the target demographic of this tool does not include those of us who are not that adept to coding, then please disregard my comments. But if we are in the target, please consider.
Thank you very much, Purelife

[purelife]

BTW: you should also visit my blog in the signature. it'll be a great 'start here'.

Thank you for all your help...I am starting to make some headway...

I'll give your blog some traffic when I get some time

[purelife]

So I am making some headway...but I am having some issues switching between programs now.

I am in excel...manipulate the data, copy it...
now I want to dump it into an access table that is open
the QM keeps getting hung up after this line

Code:

Copy      Help

,int w2 = win("2009 Q1 Data")
,act w2

It tells me Error (RT) in Keep This: cannot activate window

"2009 Q1 Data" is the Access table database....

Any help is greatly appreciated and thank you!

[Gintaras]

Maybe the window that win finds is not the window that normally is active. MS Office apps sometimes have this problem.

Activate Access manually and run this macro using hotkey. It displays 2 numbers in QM output pane. Are they equal?
Macro Macro1301

Code:

Copy      Help

int w2 = win("2009 Q1 Data")
out w2

int activeWindow=win
out activeWindow

[purelife]

Maybe the window that win finds is not the window that normally is active. MS Office apps sometimes have this problem.

Activate Access manually and run this macro using hotkey. It displays 2 numbers in QM output pane. Are they equal?
Macro Macro1301

Code:

Copy      Help

int w2 = win("2009 Q1 Data")
out w2

int activeWindow=win
out activeWindow

Gintaras,
Thank you for the reply...

No they are not

6685976
2163448

But when I change the code to

Code:

Copy      Help

int w2 = win("2009 Q4 Data" "")
out w2

act win("2009 Q4 Data" "")
int activeWindow=win
out activeWindow

They are

6685976
6685976

[purelife]

When I run the Macro...it will work fine for about 5~10 itterations...

Then it gets to the previously mentioned code and I get
Error (RT) in Keep This: cannot activate window

[Gintaras]

Now I cannot say why it happens. Try to handle the error with err.

For example:

Macro Macro1302

Code:

Copy      Help

,int k
,for k 0 1000000
,,act w2
,,err
,,,if(k<50) ;;5 s
,,,,wait 0.1
,,,else
,,,,OnScreenDisplay _error.description 5 0 0 "" 0 0xFF
,,,,ret

(replace act w2 with this code)

It tries to activate many times. If after 5 s still cannot activate, ends macro.

[purelife]

Now I cannot say why it happens. Try to handle the error with err.

For example:

Macro Macro1302

Code:

Copy      Help

,int k
,for k 0 1000000
,,act w2
,,err
,,,if(k<50) ;;5 s
,,,,wait 0.1
,,,else
,,,,OnScreenDisplay _error.description 5 0 0 "" 0 0xFF
,,,,ret

(replace act w2 with this code)

It tries to activate many times. If after 5 s still cannot activate, ends macro.

Thank you but the macro already ends after a few seconds...this does not make any sense why it is happening

I just had it run about 30 or so out of the 100 interations just fine...then it stopped...

When this happens I have to delete all the data, and try again to hope it will run the entire time

Is there a different way to call an Access Table?

[Gintaras]

Possibly exist other ways to send data to Access, but it is not simple.

What about this:

Macro Macro1305

Code:

Copy      Help

,act w2
,err
,,mes "Failed to activate Access. Please activate it manually and then click OK." "" "!"
,,0.5

If fails to activate Access, shows message box. The you activate Access, click OK, and the macro continues.

[purelife]

Possibly exist other ways to send data to Access, but it is not simple.

What about this:

Macro Macro1305

Code:

Copy      Help

,act w2
,err
,,mes "Failed to activate Access. Please activate it manually and then click OK." "" "!"
,,0.5

If fails to activate Access, shows message box. The you activate Access, click OK, and the macro continues.

GREAT! That seems to work...but here is the funny thing

So say the first 3 iterations work properly...then what would have typically happened was that error.
Now with your error code, the box comes up, and I click okay..and it continues like nothing happened

So I am wondering could it just be my system running out of resources at some point, and with that error box coming up gives it enough time to catch up with itself? I am copying 13 columns every time, and generally atleast 100 rows...sometimes 3~400.

Thanks again!

[Gintaras]

It can be one of reasons.

Other possible reason - if Access for some reason creates some hidden window or tooltip with similar name, the macro finds that window, but that window cannot be activated. To make macros more reliable, always use class name in win.

w2=win("window name" "window class name")

QM records windows with name and class name.

Third reason - Windows foreground lock feature. For some reason it may not allow QM to activate a window. But normally it is rare.

Reason 4 - something that I don't know.

[purelife]

I thought that class name may have something to do with it....

Is there a listing of what are available class names...I am not really sure what the means/where to find it

[Gintaras]

Class name is displayed in QM status bar when mouse is over that window.

Also it is recorded.

If don't want to start recording: move mouse over that window, press Ctrl+Shift+Alt+W. QM will show a menu. Select the first item. It inserts in QM:
int hwnd=win("name" "class")

If want a listing: open 'Find Window or Control" dialog (in floating toolbar) and you will find windows in the first combo box. Select an item and it will give win with class name.

If want to see properties of all visible and hidden windows, use 'Explore windows' dialog. In floating toolbar, 'More Tools' menu.