Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Using Vbs refresh bloomberg data in an excel file and copy paste value the result from colum p to colum o

Posted on 2011-02-25
5
Medium Priority
?
1,080 Views
Last Modified: 2014-01-01
I was able to use a program from this web site that refresh the bloomberg data but i need to copy paste special tose data and save the file and close it. Can somoene help. it is the first time I used VBS.

Dim objShell:Set objShell=CreateObject("Wscript.Shell")
sRun="cmd /k Start excel " & chr(34) & "N:\TO\file.xlsm" & chr(34)
objShell.Run sRun
wscript.sleep 5000
objShell.AppActivate "Window title" 'of the program's popup
objShell.SendKeys "~" 'send Enter key

Set objShell=Nothing

Thank you very much for your help
0
Comment
Question by:A05
  • 2
3 Comments
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 1000 total points
ID: 34985977
Here is a very basic vbscipt to interact with Excel. Please amend it for realistic situations :)

This code

1) Opens a file called MyFile.Xlsm
2) Enables calculation so that the data is updated.
3) Copies Data from Cell A2 and pastes it in Cell B2 and then closes the workbook after saving it.

Code

Dim xlApp, xlBook, xlSht
Dim filename, value1, value2, value3, value4

filename = "c:\MyFile.xlsm"

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(filename)
Set xlSht = xlApp.ActiveSheet

xlApp.Calculate
xlSht.Cells(2, 1).Copy
xlSht.Cells(2, 2).Paste

xlBook.Close FTruealse
xlApp.Quit

Set xlSht = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

Open in new window


Sid
0
 
LVL 15

Assisted Solution

by:markdmac
markdmac earned 1000 total points
ID: 34987196
In SIDS script, there is a typo in line 14. That should read either True or False.

You could also combine lines 11 & 12 like this.

Xlsht.cells(2,2).value = xlsht.cells(2,1).value
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34987211
Thanks markdmac. :)

Sid
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently I finished a vbscript that I thought I'd share.  It uses a text file with a list of server names to loop through and get various status reports, then writes them all into an Excel file.  Originally it was put together for our Altiris server…
In this article we want to have a look at the directory attributes which are used by Microsoft to store the so called Security Identifiers (SID). These SIDs plays an important role in delegating and granting permissions and in authentication of trus…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Screencast - Getting to Know the Pipeline
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question