Refreshing Data from an External Source using VB

[petehenson]

Hi Folks,

I hope you can help. I have created a macro which copies a product code from one tab ("Macro") pastes it in an Output sheet ("Output"), this output sheet is full of data connections - most of them to an external company's data server. The idea is once the code is pasted in the data fields refresh; the macro then saves the file as "Fund Name" + "Today's Date".xls
Now then, the problem occurs after the product code is pasted. The data connections do no refresh until AFTER the macro finishes - thus meaning the sheet saves with incorrect data.
I've tried the following:

• Entering an Application.Wait command
• Split the macros into two and getting the first part to call the second part
• Adding a third macro between the two which just waits
• Tried doing a rebuild of the worksheets before the save section

NONE of these have worked.

Basically the data wont refresh until the macro has stopped running.

I hope this makes SOME sense - my code is below:

Sub CopyCodeToOutput()
'
' CopyCodeToOutput Macro
'
    Sheets("MACRO").Select
    Range("B1").Select
    Selection.Copy
    Sheets("OUTPUT").Select
    Range("A1").Select
    ActiveSheet.Paste '
    Sheets("MACRO").Select
'This bit deletes the top row so the macro can be looped for a list of products
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    Sheets("OUTPUT").Select
    ActiveWorkbook.Save
    Sheets("OUTPUT").Select
       
'This bit Saves a copy of the sheet as the Product name + date      
    ActiveWorkbook.SaveCopyAs Filename:="N:\FMR\Recommended Funds\" & Range("A101").Value & " " & Range("A1").Value & " " & Format(Date, "yyyymmdd") & ".xls"
    Windows("DRAFT Recommended Funds Data Collection - Master.xls").Activate
    MsgBox Range("A101").Value & Range("A1").Value
    
    
End Sub

Any help would be V much appreciated!
Pete

 

[doofusboy]

Have you tried using 'DoEvents' for the refresh and then continuing with your macro?

 

[petehenson]

I've not tried this...I'm pretty rubbish at scripting (as you can probably see!)

How would I do this??

Thanks for your time

Pete

 

[petehenson]

Anybody??