Run code after Data Import from Google Docs

[MarkCBB]

HI there VBA Gurus,

I am looking for some code that is triggered after a web query has run. to run the following code:

Sub A()

    Rows("1:130").Delete Shift:=xlUp
With Cells
    .Replace What:="?»?", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    .ClearFormats
End With
End Sub

 

[John_w]

There are 3 ways I can think of:

1. Call your code in the Worksheet_Change event on the web query sheet. You may need to experiment to determine if all the data for the web query has been retrieved. One way which might work is:

Private Sub Worksheet_Change(ByVal Target As Range)
        
    If Not Intersect(QueryTables(1).Destination, Target) Is Nothing Then
        
        'All data for web query has been retrieved

        A 'call your code
    End If

End Sub

2. Call your code in the web query's AfterRefresh event. http://support.microsoft.com/kb/213187 shows how to set up the event handler.

3. Refresh the web query yourself in VBA code rather than via the Excel GUI and call your code after the .Refresh call, something like this:

Dim qt As QueryTable
Set qt = Sheets(1).querytables(1)
qt.Refresh BackgroundQuery:=False
A 'call your code

 

[MarkCBB]

Hi there John,

Thank you for taking the time to send me all the options, been having a bit of fun with the codes, I am going to use the 3rd option.

I take it that you have had some expiriance pulling data from Google docs? have you run into any challanges with the data? or do you have any tips I would possible use?

thank you once again, your responce is very helpfull to me.

Kindest Regards,
Mark Blackburn

 

[John_w]

I'm glad my response was helpful. The 3rd option is the easiest to implement, because you can control when the web query retrieves the data and know that the data is complete before manipulating it.

Sorry, but I've never written any code for Google Docs, so don't know what's involved.