Greetings,
By libraries do you mean you referenced Excel? Under Tools - References
- Microsoft Excel x....
I am assuming you exported the modules from Excel VBA and brought them
into BWS?
I've been successful with Excel Macros using two different approaches.
Approach 1 is the "easiest" -but sounds like won't work because of your
security settings - it will display the security warnings etc if macros
are turned off. I'll present it here just for others.
In Declarations section of VBA add:
Dim o as Object
In your code
D.Open_ "C:\ab\Book1.xlsm", ftExcel ' the spreadsheet with the macro
Set o = D.t.appl
o.Run "Macro name"
Approach 2 is to have the code inside BWS - the important thing is the
Excel specific code needs an object identifier in front of it and you
have to "wire" the code to point to spreadsheet you want to modify.
The DataStation can do that "wiring" for you though via the Open
command.
as the above...
In Declarations section of VBA add:
Dim o as Object
Dim k as Object
Add:
D.Open_ "C:\ab\Book1.xlsm", ftExcel ' the spreadsheet you want to modify
of course
Set o = D.t.appl
Then in macro code - all the stuff that is excel specific add o. in
front of the commands.
Here's an snippet of modified Excel macro code - the with eliminates the
need for o in front of everything.
With o
.Sheets("Raw data").Select
.Range("A1:D4").Select
.Selection.Copy
.Sheets("Format a").Select
.Range("A2").Select
.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End With
For Each k In o.Workbooks
k.Save
Next k
Regards,
Thom
Thom C. Blackwell
Product Manager
Boston Software Systems
(866) 653-5105 ex 807
www.bossoft.com <http://www.bossoft.com/>
Visit us at MUSE Internationl Booth 629
Sign up for my weekly webinar!
<http://www.bostonworkstation.com/customer_center/special_events.aspx>
LEGAL NOTICE Unless expressly stated otherwise, this message is
confidential and may be privileged. It is intended for the addressee(s)
only. Access to this E-mail by anyone else is unauthorized. If you are
not an addressee, any disclosure or copying of the contents of this
E-mail or any action taken (or not taken) in reliance on it is
unauthorized and may be unlawful. If you are not an addressee, please
inform the sender immediately, then delete this message and empty from
your trash.
From: [email protected] [mailto:[email protected]]
Sent: Thursday, April 16, 2009 12:48 PM
To: Talk
Subject: [talkbws] Running Excel Macros
I am trying to find a way to run Excel macros via BWS.
The Case:
BWS runs 4 reference lab reports and saves them on a network drive. I
then use Excel to combine these 4 reports into 2 and remove the garbage,
etc... Unfortunately my off network pc had a catastrophic HDD failure
and is now offline. Since I don't want to go through that again I've
gotten access to a virtual system on the network. Unfortunately the
admins have excel macro security set to medium and needs user permission
to run. I've added the libraries to BWS and can run most of the macro
via BWS, but fails on Excel VBA only terms (like Row.count). Has anyone
already come up with a solution to something like this or any ideas how
to go about coming up with a solution?
Thanks.
Brian Bennett
Affinity Health Systems
Systems Analyst
PBS\Clinic Billing
(920)628-9055
[email protected]
________________________________
CONFIDENTIALITY NOTICE: This e-mail communication and any attachments
may contain confidential and privileged information for the use of the
designated recipient(s) named above. If you are not the intended
recipient, you are hereby notified that you have received this
communication in error and that any review, disclosure, dissemination,
distribution or copying of it or its contents is prohibited. If you have
received this communication in error, please notify the sender at the
electronic mail address noted above and destroy all copies of this
communication and any attachments. Thank you for your cooperation.
---
To post a message to this list, send mail to: [email protected]
You are currently subscribed as: [email protected]
Unsubscribe in the customer center on our website:
http://www.bostonworkstation.com/customer_center/virtual_user_group_talk
.aspx
---
To post a message to this list, send mail to: [email protected]
You are currently subscribed as: [email protected]
Unsubscribe in the customer center on our website:
http://www.bostonworkstation.com/customer_center/virtual_user_group_talk.aspx
