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
801 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
Script to copy or move mouse-selected collection of files plus targets referenced by shortcuts (.lnk) The purpose of this article is to help illuminate the real challenges and options available (where they may exist) for utilizing simple scriptin…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

867 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now