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

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
A05Asked:
Who is Participating?
 
SiddharthRoutConnect With a Mentor Commented:
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
 
markdmacConnect With a Mentor Commented:
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
 
SiddharthRoutCommented:
Thanks markdmac. :)

Sid
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.