Got Ads?
  Recommendations for Excel and AdWords API
Someone on the adwordsapi google group asked about calling the AdWords API from Excel. I spent a week trying different things, and have a few recommendations.

I tried PocketSOAP, but couldn't get the WSDL generator to work with google's wsdl. You can use PocketSOAP reasonably well, if you set up the envelope and header every time by hand... but that's not very time-efficient.

I also spent a good week trying to get Excel VBA to work. The Web References didn't fully work with the Google WSDL from Excel. For example, the campaign service has an enumerated type that has "type" as a variable name, which is not allowed in VB, since it's a reserved word. Furthermore, you can't just set the headers using the SoapClient30 from Excel, you need to try and implement IHeaderHandler. I finally got it to work, but it's very difficult. It's quite flaky and I think it's a waste of time, frankly.

So, if you wish to use Excel, the answer really is to use VB.NET from Visual Studio. You create a class, then use a COM module that can been seen from the "References" of Excel. Here's a program that works in Visual Studio and can be called from Excel. In Visual Studio, you'd create a Console Application that called the module below. Then if you step thru the code, you can see the output. I'm going to try and post more detailed instructions later.

For Excel/VBA, the "PrintCampaign" sub could be modified to dump the xml into a range on a worksheet.

In summary, I really recommend not wasting your time struggling with VBA and the Web Services Toolkit. Just use Visual Studio. The WSDL parser works, you can set the headers to what google expects much more easily, and you can move past just getting the calls to work.

    ' A creatable COM class must have a Public Sub New() 
    ' with no parameters, otherwise, the class will not be 
    ' registered in the COM registry and cannot be created 
    ' via CreateObject.
    Public Sub New()
    End Sub

    Public Sub PrintCampaignList(ByVal Email, ByVal Password, ByVal Token, ByVal Agent, ByVal ClientEmail)
        Dim s As New CampaignServiceService
        s.useragentValue = New useragent
        s.useragentValue.Text = New String() {Agent}

        s.clientEmailValue = New clientEmail
        s.clientEmailValue.Text = New String() {ClientEmail}

        s.emailValue = New email
        s.emailValue.Text = New String() {Email}

        s.passwordValue = New password
        s.passwordValue.Text = New String() {Password}

        s.tokenValue = New token
        s.tokenValue.Text = New String() {Token}

        Dim campaignList() As Campaign
        Dim c As New Campaign
        campaignList = s.getAllAdWordsCampaigns(0)
        For Each c In campaignList

    End Sub

    Public Sub PrintCampaign(ByVal campaign)
        Console.WriteLine("ID: " &
        Console.WriteLine("Name: " &
        Console.WriteLine("Daily Budget: " & campaign.dailyBudget)
        Console.WriteLine("Status: " & campaign.status)
        Console.WriteLine("Start: " & campaign.startDate)
        Console.WriteLine("EndDate: " & campaign.endDate)
    End Sub

End Class

[+/-] Continue Reading...

Links to this post:

Create a Link

<< Home

Subscribe to GotAds?


Recent Posts

Recommendations for Excel and AdWords API


February 2005 /  March 2005 /  April 2005 /  May 2005 /  June 2005 /  July 2005 /  August 2005 /  September 2005 /  October 2005 /  November 2005 /  December 2005 /  January 2006 /  February 2006 /  March 2006 /  April 2006 /  May 2006 /  June 2006 /  July 2006 /  August 2006 /  September 2006 /  October 2006 /  November 2006 /  December 2006 /  January 2007 /  February 2007 /  March 2007 /  April 2007 /  May 2007 /  June 2007 /  July 2007 /  August 2007 /  September 2007 /  October 2007 /  November 2007 /  December 2007 /  January 2008 /  February 2008 /  March 2008 /  April 2008 /  May 2008 /  June 2008 /  July 2008 /  August 2008 /  September 2008 /  November 2008 /  December 2008 /  January 2009 /  March 2009 /