Special Copy Paste in VBA

Question

This is a Copy Paste question using VBA. I basically want to grab a number from many different workbooks. My problem is this number is contantly updating so I don't think I can have the last vesion of the number without opening the workbooks. The workbooks are big and take time to open and load data (taking data from an external source (bloomberg)). My first clue was to open the workbooks, then let them charge then taking the data from it. My problem is, I need to set up a timer to do so(and let my workbook load) and during this timer my workbook cannot upload the datas so I end up with my old datas...

So the only solution I found was to do two different macros. One open all the workbook, then the second close and save it and grab the data from it...

Do you guys have any better idea?

My problem is, the workbooks are so big I need to open them 5 by 5 maximum and do it every time...

I copy paste you my code, I know it is very basic, if you think of any better code or way to make it work, please let me know.

Sub OpenWorkbooks() workbooks.Open Filename :="C/.../file1.xlsx" workbooks.Open Filename :="C/.../file2.xlsx" workbooks.Open Filename :="C/.../file3.xlsx" . . End sub

Now the one to close and then copy paste the value as written by someone else.

Sub GetNumber() Dim wWbPath As String, WbName As String Dim WsName As String, CellRef As String Dim Ret As String Workbooks("file1").Close SaveChanges:=True wbPath = "C:/etc...." WbName = "file1.xlsx" WsName = "Sheet1" CellRef = "AD30" arg = "'" & wbPath & "[" & wbName & "]" & _ wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1) Worksheets("Sheet1").Range("A1") = ExecuteExcel4Macro(arg) End sub

As said do you think there is a better way to do it? I wanted to do the whole steps for each file but I cannot since the timer stop the refresh of my spreadhseet and I need a timer since my worksheets needs time to load. So 1 - Open all , 2- close all and save all and copy the numbers... But I need to do it by batch cause if I open all it crashes...

My other question is, how could I make the code better? Ultimately I would like to have in my open workbook a spreadsheet where I would have all the paths for each worbook to open and copy/paste. then I would like a macro which loop the path, open the workbook wait (but timer not working...) then copy paste and close.

The altrnative is always looping the paths but by blocks. I will put all my paths on cells A1:A10 and I could have a loops opening the path present in A1:A10, then a second macro closing and saving the workbook with the path in A1:A10

Any ideas are welcome,

Thank you

Take this as you will, but many languages can open excel files. You may find the process of interacting programmatically with a different language, perhaps python, to be substantially easier than writing macros for Excel itself. This looks like a darn good intro for python : automatetheboringstuff.com/chapter12. — erik258
– erik258, Commented Dec 4, 2017 at 23:40
@DanFarrell Thank you for your reply! Unfortunately it need to be VBA application Button type since I will not be the user of it... I would prefer Python too... — user6457870
– user6457870, Commented Dec 4, 2017 at 23:44
We need more information, but from what you have written I think you are making it difficult. You should have only one code that opens the workbooks one by one and copies whatever data you need. This way you will get the updated data and you won't need much memory. When you open the workbooks, are they connected to some external resources that require updating? — Ibo
– Ibo, Commented Dec 4, 2017 at 23:51
@Ibo Thank you for your reply Thats my problem. The workbook need time to refresh cause real feed data are arriving from an external souce. So I need to set up a timer to be sure that the workbook is well refresher before the second part of my macro copy past it. the problem is... during this timer, my workbook STOP to refresh. If I set up 10 second laps time, during that 10 secs, my workbook will also stop to refresh... So its not possible that way... — user6457870
– user6457870, Commented Dec 4, 2017 at 23:54
@DanFarrell It's pretty hard to argue that automating Office with Python is easier than with VBA. That's what VBA is built for. In fact, that's arguably the only thing VBA is better at than Python. — Jean-François Corbett
– Jean-François Corbett, Commented Dec 5, 2017 at 8:43

Johnny D · Accepted Answer · 2017-12-05 05:16:11Z

Sub testopenRefreshClose() Call openRefreshClose("C/.../file1.xlsx") Call openRefreshClose("C/.../file1.xlsx") End Sub Sub openRefreshClose(wbPathName As String) Dim wb As Workbook Set wb = Workbooks.Open(wbPathName) ' open and assign to workbook object wb.RefreshAll ' refresh the data wb.Close SaveChanges:=True ' close the workbook End Sub

This appears to finish running the query before closing, but you can also try unchecking enable background refresh which will ensure the data is updated before other actions are taken.

Thank you for your reply. Unfortunately it is not refreshing the API data inside for some reasons... In some cells, there is formula which are lively taking data from outside. Looks like the book open then directly close without updating it... Any clue?
Or it do not let enough time, so instead of the value inside, there #VALUE! Since it hadn't had enough time to load. I tried by adding Application.CalculateUntilAsyncQueriesDone as advised by Ibo, it change nothing unfortunately. (Conection properties is greyed)

Ibo · Accepted Answer · 2017-12-05 00:37:04Z

You can achieve this in different ways. One is to store the values in a sheet or database and every time that you want to refresh and get new values compare the new value to the old value and if it was different (cell value updated for sure) then continue the code. Just to make the code get out of waiting you should set a max time like an expiration time. I would do an iteration of 20 times, each for 5 seconds. So basically you should have a setup like this in your code:

for i=1 to 20 newHour = Hour(Now()) newMinute = Minute(Now()) newSecond = Second(Now()) + 5 waitTime = TimeSerial(newHour, newMinute, newSecond) ' ~~> get new value here and compare it to the old value next i Application.Wait waitTime if newValue<>oldValue then exit for else 'do nothing, just wait end if

or you can simply let Windows take the control, finish the task and continue:

'~~> your code here before refresh Activeworkbook.RefreshAll DoEvents '~~> rest of your code here after refresh

DoEvents let's Windows to momentarily take a break from Macro to process all pending events before returning to the macro. That means it will refresh your workbook from the external resource and then continue executing the rest of code.

If you are using Excel 2010 and higher, there is a control that may work better than DoEvents:

 ThisWorkbook.RefreshAll 'or activeworkbook, choose as you need Application.CalculateUntilAsyncQueriesDone 'the code execution will halt here until the refresh job is done

Collectives™ on Stack Overflow

Special Copy Paste in VBA

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related