Help With Making Web Query Dynamic Using VBA

[Protectyaneck1]

So here is my dilemma, I am attempting to make my Zip Code Workbook dynamic with a web query, but am missing something.

I have two Worksheets:
Worksheet 1- "Zip Codes" which has a complete list of every zip code in CA, with a "Command Button" inserted in with the goal to use a specific zip code "E3" as a reference in the query.

Worksheet 2- "Web Query" is the sheet that my webquery is connected to. I have changed the external data range properties name to "Zip Code"
Attached is the link to the website in question, with a search, with the goal to pull the table with the information: zip, county, population, etc...
SearchBug - ZIP Code Lookup

-Problem- It appears when I click the the "Command Button", the web query gets refreshed, however, the macro is not correctly applying the "zip code" to the end of the URL correctly.

VBA Project for CommandButton1

Private Sub CommandButton1_Click()

getzipcodes zipcode = Range("E3").Value

End Sub


Module1 for Web Query

Public Sub getzipcodes(byvalzipcode As String)

ThisWorkbook.Sheets("Web Query").QueryTables("Zip Code").Connection = "url;http://www.searchbug.com/tools/zip-code-lookup.aspx?TYPE=zip2city&ZIP=" & zipcode

ThisWorkbook.Sheets("Web Query").QueryTables("Zip Code").Refresh

End Sub


I am new to VBA's and Macros, so I greatly appreciate any feedback.

Thank you,

 

[Rijnsent]

Hi Yaneck,
try something like this for your first bit:

Private Sub CommandButton1_Click()

Dim ZP as string
ZP = Worksheets("Sheet1").Range("E3").Value
call getzipcodes(ZP)

End Sub

Great VBA courses: Excel VBA Programming - a free course for complete beginners or Free VBA Training Course

Cheers,

Koen

 

[Protectyaneck1]

Sorry for the delayed response. but thanks for the response! Even bigger thanks for the links to the VBA training links. So awesome!!

 

[BQardi]

Did you copy this code directly from your project? If so the error lies with the variable "zipcode" or rather "byvalzipcode":

Public Sub getzipcodes([COLOR=#ff0000]byvalzipcode [/COLOR]As String) [COLOR=#008000]'There should be a space: "[/COLOR][COLOR=#ff0000]ByVal zipcode[/COLOR][COLOR=#008000]" or delete "[/COLOR][COLOR=#ff0000]ByVal [/COLOR][COLOR=#008000]" all together, it is not needed.[/COLOR]

ThisWorkbook.Sheets("Web Query").QueryTables("Zip Code").Connection = "url;http://www.searchbug.com/tools/zip-code-lookup.aspx?TYPE=zip2city&ZIP=" & [COLOR=#ff0000]zipcode[/COLOR]

ThisWorkbook.Sheets("Web Query").QueryTables("Zip Code").Refresh

End Sub

The code "thinks" your variable is called "byvalzipcode" when in fact you are using only "zipcode".
So "zipcode" would be empty, while "byvalzipcode" would hold your value (zip-code), but obviously isn't used in your code.