I have a spreadsheet that is used to generate quotes i.e. we enter in various data and using the spreadsheets formulas it works out the cost and time for us. This information is then manually copied into a Word document and formatted into an official quote and it is this process I would like to automate with a macro.
I'm trying to work out how best to get the macro to copy the data from specific fields and store them for pasting into Word later, for example:
D3 = Staff Name (variable $sname)
D5 = Client Name (variable $cname)
D7 = Company Name (variable $ccompany)
D9 = Word Count (variable $wcount)
G9 = A4 Pages (variable $pages)
It then gets a little more complicated.
C16 = Audio CD Price (but this will only show a positive value if another checkbox is selected i.e. if the client wants an Audio CD. Therefore only if the value is greater than £0.00 do I need it to be copied)
E16 = Copies price (this will only show a positive value if multiple copies are requested so I only want this cell copied if it's value is greater than £0.00
What I would like is for the macro to extract all of this data from the spreadsheet in one go storing each cell as a different variable and to then paste all of this information along with the rest of the letter that makes up the quote in one go e.g.
Dear $cname,
Thank you for your enquiry....
I'm not sure on the best way of doing this primarily because I'm not sure how best to get the macro to navigate to the specific fields. I've searched these forums and found examples where people have used VBScript but I don't know anything about VBScript and so can't write it or understand what the examples do or how they work. Is this something that is possible to do without VBscript or have I bitten off more than I can chew?
Copy Data From Specific Excel Fields?
Moderators: Dorian (MJT support), JRL
-
Marcus Tettmar
- Site Admin
- Posts: 7395
- Joined: Thu Sep 19, 2002 3:00 pm
- Location: Dorset, UK
- Contact:
Hi,
You can use XLGetCell to get a cell from Excel. See XLOpen, XLGetCell etc in the help file and look at the example macros included with the software - there's one that demonstrates XLGetCell.
Or you could read the entire worksheet into an array using DBQuery:
http://www.mjtnet.com/blog/2008/04/16/r ... g-dbquery/
Or you could use DDEQuery.
Or you could use VBScript and COM to control Excel more directly.
Or you could even simulate a user copying from the cell but given the above methods exist that would be silly.
You can use XLGetCell to get a cell from Excel. See XLOpen, XLGetCell etc in the help file and look at the example macros included with the software - there's one that demonstrates XLGetCell.
Or you could read the entire worksheet into an array using DBQuery:
http://www.mjtnet.com/blog/2008/04/16/r ... g-dbquery/
Or you could use DDEQuery.
Or you could use VBScript and COM to control Excel more directly.
Or you could even simulate a user copying from the cell but given the above methods exist that would be silly.
Marcus Tettmar
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar
Did you know we are now offering affordable monthly subscriptions for Macro Scheduler Standard?
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar
Did you know we are now offering affordable monthly subscriptions for Macro Scheduler Standard?
Cheers for that.
I had seen XLGetCell used in another forum post but is this a feature of a more recent release of MS because I can't see it in my version anywhere. I'm using MS 10?
At the moment I'm getting somewhere by simulating a user copying the relevant cells but I keep getting random "Cannot Empty Clipboard" messages which I'm currently trying to solve
I had seen XLGetCell used in another forum post but is this a feature of a more recent release of MS because I can't see it in my version anywhere. I'm using MS 10?
At the moment I'm getting somewhere by simulating a user copying the relevant cells but I keep getting random "Cannot Empty Clipboard" messages which I'm currently trying to solve
Okay I've solved the Cannot Empty Clipboard error but now something odd is happening that I don't understand.
I've got the macro to go to each required cell, copy it's content and store it as a variable, for ease of reading lets call them:
$var1
$var2
$Var3
After copying all the data I then get the macro to generate the Word Document and get it to paste these variables into the document so:
Send>To:
WaitClipBoard
PutClipBoard>$var1
Press Ctrl
Send>v
Release Ctrl
Wait>1
Press Enter
Send>Company:
WaitClipBoard
PutClipBoard>$var2
Press Ctrl
Send>v
Release Ctrl
However what actually happens is that the first text to be pasted is actually that of $var2, the 2nd bit of text to be pasted is actually that of $var3. i.e. $var1 is never pasted and everything is out by one.
I'm completely stumped by why this is happening, I've tried changing the names of the variables in question but no joy. Any ideas?
I've got the macro to go to each required cell, copy it's content and store it as a variable, for ease of reading lets call them:
$var1
$var2
$Var3
After copying all the data I then get the macro to generate the Word Document and get it to paste these variables into the document so:
Send>To:
WaitClipBoard
PutClipBoard>$var1
Press Ctrl
Send>v
Release Ctrl
Wait>1
Press Enter
Send>Company:
WaitClipBoard
PutClipBoard>$var2
Press Ctrl
Send>v
Release Ctrl
However what actually happens is that the first text to be pasted is actually that of $var2, the 2nd bit of text to be pasted is actually that of $var3. i.e. $var1 is never pasted and everything is out by one.
I'm completely stumped by why this is happening, I've tried changing the names of the variables in question but no joy. Any ideas?
-
Marcus Tettmar
- Site Admin
- Posts: 7395
- Joined: Thu Sep 19, 2002 3:00 pm
- Location: Dorset, UK
- Contact:
Why are you putting data onto the clipboard to then paste when you could send the data directly:
Send>$var1
Press Enter
Send>$var3
Press Enter
Send>$var4
Not that this answers why you're seeing the values differently to what you expect. What does the watch list show them as?
Send>$var1
Press Enter
Send>$var3
Press Enter
Send>$var4
Not that this answers why you're seeing the values differently to what you expect. What does the watch list show them as?
Marcus Tettmar
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar
Did you know we are now offering affordable monthly subscriptions for Macro Scheduler Standard?
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar
Did you know we are now offering affordable monthly subscriptions for Macro Scheduler Standard?
Yeah I spotted that just after I posted my last reply and changed it accordingly. However I still can't get it to work and find that I can never get the first Excel cell that is copied and stored as a variable to be pasted into anything else or displayed as a message or anything.
The code so far is:
Sometimes when I run the macro I when it should be pasting %$cContact% it pastes %$dDate%, other times it pastes nothing.
I've also tried using DDERequest such as:
But no matter what cells and rows I enter the result is always blank!
Why would the DDERequest just fail to work?
The code so far is:
Code: Select all
//Copy Transcription Officer Name
SetFocus>Microsoft Excel - Calculator.xls
WaitWindowOpen>Microsoft Excel - Calculator.xls
Wait>1
Press Ctrl
Send>g
Release Ctrl
WaitWindowOpen>Go To
Wait>1
Send>D3
Press Enter
WaitWindowClosed>Go To
SetFocus>Microsoft Excel - Calculator.xls
WaitClipBoard
Wait>1
Press Ctrl
Send>c
Release Ctrl
GetClipBoard>$sName
Wait>1
//Copy Date
Press Ctrl
Send>g
Release Ctrl
WaitWindowOpen>Go To
Send>I3
Wait>1
Press Enter
WaitWindowClosed>Go To
SetFocus>Microsoft Excel - Calculator.xls
WaitClipBoard
Wait>1
Press Ctrl
Send>c
Release Ctrl
GetClipBoard>$dDate
Wait>1
//Copy Contact Name
Press Ctrl
Send>g
Release Ctrl
WaitWindowOpen>Go To
Send>D5
Wait>1
Press Enter
WaitWindowClosed>Go To
SetFocus>Microsoft Excel - Calculator.xls
WaitClipBoard
Wait>1
Press Ctrl
Send>c
Release Ctrl
GetClipBoard>$cContact
Wait>1
//Copy Organisation
Press Ctrl
Send>g
Release Ctrl
WaitWindowOpen>Go To
Send>D7
Wait>1
Press Enter
WaitWindowClosed>Go To
SetFocus>Microsoft Excel - Calculator.xls
WaitClipBoard
Wait>1
Press Ctrl
Send>c
Release Ctrl
GetClipBoard>$cCompany
Wait>1
//Copy Word Count
Press Ctrl
Send>g
Release Ctrl
WaitWindowOpen>Go To
Send>D9
Wait>1
Press Enter
WaitWindowClosed>Go To
SetFocus>Microsoft Excel - Calculator.xls
WaitClipBoard
Wait>1
Press Ctrl
Send>c
Release Ctrl
GetClipBoard>$wCount
Wait>1
//Get Number of A4 Pages
Press Ctrl
Send>g
Release Ctrl
WaitWindowOpen>Go To
Send>G9
Wait>1
Press Enter
WaitWindowClosed>Go To
SetFocus>Microsoft Excel - Calculator.xls
WaitClipBoard
Wait>1
Press Ctrl
Send>c
Release Ctrl
GetClipBoard>$pCount
Wait>1
//Copy Braille Master Copy Price
Press Ctrl
Send>g
Release Ctrl
WaitWindowOpen>Go To
Send>C16
Wait>1
Press Enter
WaitWindowClosed>Go To
SetFocus>Microsoft Excel - Calculator.xls
WaitClipBoard
Wait>1
Press Ctrl
Send>c
Release Ctrl
GetClipBoard>$bMaster
Wait>1
//Copy Braille Copy Price
Press Ctrl
Send>g
Release Ctrl
WaitWindowOpen>Go To
Send>E16
Wait>1
Press Enter
WaitWindowClosed>Go To
SetFocus>Microsoft Excel - Calculator.xls
WaitClipBoard
Wait>1
Press Ctrl
Send>c
Release Ctrl
GetClipBoard>$bCopy
Wait>1
//Copy Braille Total Price
Press Ctrl
Send>g
Release Ctrl
WaitWindowOpen>Go To
Send>G16
Wait>1
Press Enter
WaitWindowClosed>Go To
SetFocus>Microsoft Excel - Calculator.xls
WaitClipBoard
Wait>1
Press Ctrl
Send>c
Release Ctrl
GetClipBoard>$bTotal
Wait>1
//Copy Large Print Master Price
Press Ctrl
Send>g
Release Ctrl
WaitWindowOpen>Go To
Send>C17
Wait>1
Press Enter
WaitWindowClosed>Go To
SetFocus>Microsoft Excel - Calculator.xls
WaitClipBoard
Wait>1
Press Ctrl
Send>c
Release Ctrl
GetClipBoard>$lpMaster
Wait>1
//Copy Large Print Copy Price
Press Ctrl
Send>g
Release Ctrl
WaitWindowOpen>Go To
Send>E17
Wait>1
Press Enter
WaitWindowClosed>Go To
SetFocus>Microsoft Excel - Calculator.xls
WaitClipBoard
Wait>1
Press Ctrl
Send>c
Release Ctrl
GetClipBoard>$lpCopy
Wait>1
//Copy Large Print Total Price
Press Ctrl
Send>g
Release Ctrl
WaitWindowOpen>Go To
Send>G17
Wait>1
Press Enter
WaitWindowClosed>Go To
SetFocus>Microsoft Excel - Calculator.xls
WaitClipBoard
Wait>1
Press Ctrl
Send>c
Release Ctrl
GetClipBoard>$lpTotal
Wait>1
//Copy Audio CD Master Price
Press Ctrl
Send>g
Release Ctrl
WaitWindowOpen>Go To
Send>C18
Wait>1
Press Enter
WaitWindowClosed>Go To
SetFocus>Microsoft Excel - Calculator.xls
WaitClipBoard
Wait>1
Press Ctrl
Send>c
Release Ctrl
GetClipBoard>$cdMaster
Wait>1
//Copy Audio CD Copy Price
Press Ctrl
Send>g
Release Ctrl
WaitWindowOpen>Go To
Send>E18
Wait>1
Press Enter
WaitWindowClosed>Go To
SetFocus>Microsoft Excel - Calculator.xls
WaitClipBoard
Wait>1
Press Ctrl
Send>c
Release Ctrl
GetClipBoard>$cdCopy
Wait>1
// Copy Audio CD Total Price
Press Ctrl
Send>g
Release Ctrl
WaitWindowOpen>Go To
Send>G18
Wait>1
Press Enter
WaitWindowClosed>Go To
SetFocus>Microsoft Excel - Calculator.xls
WaitClipBoard
Wait>1
Press Ctrl
Send>c
Release Ctrl
GetClipBoard>$cdTotal
Wait>1
// Copy Audio Tape Master Price
Press Ctrl
Send>g
Release Ctrl
WaitWindowOpen>Go To
Send>C19
Wait>1
Press Enter
WaitWindowClosed>Go To
SetFocus>Microsoft Excel - Calculator.xls
WaitClipBoard
Wait>1
Press Ctrl
Send>c
Release Ctrl
GetClipBoard>$atMaster
Wait>1
// Copy Audio Tape Copy Price
Press Ctrl
Send>g
Release Ctrl
WaitWindowOpen>Go To
Send>E19
Wait>1
Press Enter
WaitWindowClosed>Go To
SetFocus>Microsoft Excel - Calculator.xls
WaitClipBoard
Wait>1
Press Ctrl
Send>c
Release Ctrl
GetClipBoard>$atCopy
Wait>1
// Copy Audio Tape Transfer Charge
Press Ctrl
Send>g
Release Ctrl
WaitWindowOpen>Go To
Send>F19
Wait>1
Press Enter
WaitWindowClosed>Go To
SetFocus>Microsoft Excel - Calculator.xls
WaitClipBoard
Wait>1
Press Ctrl
Send>c
Release Ctrl
GetClipBoard>$atTransfer
Wait>1
// Copy Audio Tape Total Charge
Press Ctrl
Send>g
Release Ctrl
WaitWindowOpen>Go To
Send>G19
Wait>1
Press Enter
WaitWindowClosed>Go To
SetFocus>Microsoft Excel - Calculator.xls
WaitClipBoard
Wait>1
Press Ctrl
Send>c
Release Ctrl
GetClipBoard>$atTotal
Wait>1
// Copy DAISY Master Price
Press Ctrl
Send>g
Release Ctrl
WaitWindowOpen>Go To
Send>C20
Wait>1
Press Enter
WaitWindowClosed>Go To
SetFocus>Microsoft Excel - Calculator.xls
WaitClipBoard
Wait>1
Press Ctrl
Send>c
Release Ctrl
GetClipBoard>$dMaster
Wait>1
// Copy DAISY Copy Price
Press Ctrl
Send>g
Release Ctrl
WaitWindowOpen>Go To
Send>E20
Wait>1
Press Enter
WaitWindowClosed>Go To
SetFocus>Microsoft Excel - Calculator.xls
WaitClipBoard
Wait>1
Press Ctrl
Send>c
Release Ctrl
GetClipBoard>$dCopy
Wait>1
//Copy DAISY Total Price
Press Ctrl
Send>g
Release Ctrl
WaitWindowOpen>Go To
Send>G20
Wait>1
Press Enter
WaitWindowClosed>Go To
SetFocus>Microsoft Excel - Calculator.xls
WaitClipBoard
Wait>1
Press Ctrl
Send>c
Release Ctrl
GetClipBoard>$dTotal
Wait>1
// Copy Electronic Master Price
Press Ctrl
Send>g
Release Ctrl
WaitWindowOpen>Go To
Send>C21
Wait>1
Press Enter
WaitWindowClosed>Go To
SetFocus>Microsoft Excel - Calculator.xls
WaitClipBoard
Wait>1
Press Ctrl
Send>c
Release Ctrl
GetClipBoard>$eMaster
Wait>1
// Copy Electronic Copy Price
Press Ctrl
Send>g
Release Ctrl
WaitWindowOpen>Go To
Send>E21
Wait>1
Press Enter
WaitWindowClosed>Go To
SetFocus>Microsoft Excel - Calculator.xls
WaitClipBoard
Wait>1
Press Ctrl
Send>c
Release Ctrl
GetClipBoard>$eCopy
Wait>1
//Copy Electronic Total Price
Press Ctrl
Send>g
Release Ctrl
WaitWindowOpen>Go To
Send>G21
Wait>1
Press Enter
WaitWindowClosed>Go To
SetFocus>Microsoft Excel - Calculator.xls
WaitClipBoard
Wait>1
Press Ctrl
Send>c
Release Ctrl
GetClipBoard>$eTotal
Wait>1
// Copy Translation Price
Press Ctrl
Send>g
Release Ctrl
WaitWindowOpen>Go To
Send>C22
Wait>1
Press Enter
WaitWindowClosed>Go To
SetFocus>Microsoft Excel - Calculator.xls
WaitClipBoard
Wait>1
Press Ctrl
Send>c
Release Ctrl
GetClipBoard>$tMaster
Wait>1
//Copy Translation Total Price
Press Ctrl
Send>g
Release Ctrl
WaitWindowOpen>Go To
Send>G22
Wait>1
Press Enter
WaitWindowClosed>Go To
SetFocus>Microsoft Excel - Calculator.xls
WaitClipBoard
Wait>1
Press Ctrl
Send>c
Release Ctrl
GetClipBoard>$tTotal
Wait>1
//Copy Final Total
Press Ctrl
Send>g
Release Ctrl
WaitWindowOpen>Go To
Send>G23
Wait>1
Press Enter
WaitWindowClosed>Go To
SetFocus>Microsoft Excel - Calculator.xls
WaitClipBoard
Wait>1
Wait>1
Press Ctrl
Send>c
Release Ctrl
GetClipBoard>$fTotal
Wait>1
// Start Create Word Document
SetFocus>Document1 - Microsoft Word
WaitWindowOpen>Document1 - Microsoft Word
Press Enter *2
//Bold Text
Press Ctrl
Send>b
Release Ctrl
Wait>1
Send>Transcription Services
Press Enter *2
//Bold Off
Press Ctrl
Send>b
Release Ctrl
Wait>1
Send>To: %$cContact%
Wait>1
Press Enter
Send>Company: %$cCompany%
I've also tried using DDERequest such as:
Code: Select all
DDERequest>Excel,C:\temp\calculator.xls,R5C5,myName,10
MessageModal>%myName%
Why would the DDERequest just fail to work?
This works with my version 10.1.21
The XLS I'm using looks like this:
Code: Select all
//You would set these variables as required
Input>FileName,Enter path to XLS data file
Let>ColumnName=ID
Let>IDToFind=cgn02
Let>connStr1=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%Filename%;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"
DBConnect>connStr1,dbH1
//This just selects the row where ID=cgn02
//Let>SQL1=select * from [Sheet1$] where [%ColumnName%] = #%IDToFind%#
Let>SQL1=select * from [Sheet1$] where [%ColumnName%] = '%IDToFind%'
//Do this to get everything. Must then loop through result array
//Let>SQL1=select * from [Sheet1$]
DBQuery>dbH1,SQL1,rsSheet1,nR,nF
MessageModal>nR
MessageModal>nF
MessageModal>rsSheet1_1_2
Code: Select all
|----------------------------|
| ID | Value |
|----------------------------|
|cgn01 | Dec 15th 2009 |
|----------------------------|
|cgn02 | Jan 10th 2010 |
|----------------------------|
|cgn03 | Jan 19th 2010 |
|----------------------------|
