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
902 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Independent Software Vendors: 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

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…
Well hello again!  Glad to see you've made it this far without giving up.  In this, the fourth installment of my popular series, I'm going to cover functions and subroutines, what they are, and why they are useful.  Just in case you stumbled onto th…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

737 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