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
852 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
5 Comments
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 250 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 250 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Hello again, all.  For those of you that have been following along, you'll know that this is my third article on this topic (though it is not Part III).  This article is sort of remedial, and probably the topic with which I should have started the s…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

808 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