+ Reply to Thread
Results 1 to 8 of 8

VBA - how to send POST data to a web form and get results into worksheet

Hybrid View

bravo88 VBA - how to send POST data... 01-05-2012, 04:02 AM
Kyle123 Re: VBA - how to send POST... 01-05-2012, 07:17 AM
bravo88 Re: VBA - how to send POST... 01-05-2012, 08:26 AM
bravo88 Re: VBA - how to send POST... 01-09-2012, 04:39 AM
Kyle123 Re: VBA - how to send POST... 01-09-2012, 05:18 PM
bravo88 Re: VBA - how to send POST... 01-12-2012, 09:50 AM
Kyle123 Re: VBA - how to send POST... 01-12-2012, 10:16 AM
captain smith Re: VBA - how to send POST... 07-31-2012, 11:35 AM
  1. #1
    Registered User
    Join Date
    01-05-2012
    Location
    Stavanger
    MS-Off Ver
    Excel 2010
    Posts
    5

    Question VBA - how to send POST data to a web form and get results into worksheet

    Hello all,

    If any one can help me to write a VBA macro for excel, where i could get values from a web site?

    URL is http://www.peacesoftware.de/einigewerte/co2_e.html

    where PRESSURE and TEMPERATURE are needed to get values from the table.

    Is there any option to get it done by simply putting numbers in excel and run a macro so the values for spesific temp and pressure are calculated using this website?

    Thank you for your help!

  2. #2
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,239

    Re: VBA - how to send POST data to a web form and get results into worksheet

    Something like this would be the most simple:

    Sub GetData()
    
    Dim Pressure As Double
    Dim Temperature As Double
    
    Pressure = CDbl(InputBox("Enter the Pressure"))
    Temperature = CDbl(InputBox("Enter the Temperature"))
    
    With Sheet1.QueryTables.Add("URL;http://www.peacesoftware.de/einigewerte/calc_co2.php5", Sheet1.Range("A1"))
        .PostText = "lang=english&calc=standard&druck=" & Pressure & "&druckunit=1&temperatur=" & Temperature & "&tempunit=1&Submit=Calculate"
        .RefreshStyle = xlOverwriteCells
        .SaveData = True
        .Refresh
    End With
    
    End Sub

  3. #3
    Registered User
    Join Date
    01-05-2012
    Location
    Stavanger
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: VBA - how to send POST data to a web form and get results into worksheet

    Thank you so much, it works perfect!

  4. #4
    Registered User
    Join Date
    01-05-2012
    Location
    Stavanger
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: VBA - how to send POST data to a web form and get results into worksheet

    Hello again, after some work with this project my excel became very slow and use alot of CPU. What could trigger that?

    I use 2 macros to calculate, the first macro is for 1. step and the aother one is for 2. step.

    Here is code i use:

    Sub GetData1()
    '------------------------------------------Trinn1
    Dim Pressure As Double
    Dim Temperature As Double

    Pressure = ActiveSheet.Range("B2")
    Temperature = ActiveSheet.Range("B3")

    With Ark1.QueryTables.Add("URL;http://www.peacesoftware.de/einigewerte/calc_co2.php5", Ark1.Range("A1"))
    .PostText = "lang=english&calc=standard&druck=" & Pressure & "&druckunit=1&temperatur=" & Temperature & "&tempunit=1&Submit=Calculate"
    .RefreshStyle = xlOverwriteCells
    .SaveData = True
    .Refresh
    End With

    '------------------------------------------Trinn5
    Pressure = ActiveSheet.Range("F2")
    Temperature = ActiveSheet.Range("F3")

    With Ark5.QueryTables.Add("URL;http://www.peacesoftware.de/einigewerte/calc_co2.php5", Ark5.Range("A1"))
    .PostText = "lang=english&calc=standard&druck=" & Pressure & "&druckunit=1&temperatur=" & Temperature & "&tempunit=1&Submit=Calculate"
    .RefreshStyle = xlOverwriteCells
    .SaveData = True
    .Refresh
    End With



    '-------------------------------------------Trinn3
    Pressure = ActiveSheet.Range("D2")
    Temperature = ActiveSheet.Range("D3")

    With Ark3.QueryTables.Add("URL;http://www.peacesoftware.de/einigewerte/calc_co2.php5", Ark3.Range("A1"))
    .PostText = "lang=english&calc=standard&druck=" & Pressure & "&druckunit=1&temperatur=" & Temperature & "&tempunit=1&Submit=Calculate"
    .RefreshStyle = xlOverwriteCells
    .SaveData = True
    .Refresh
    End With


    '-------------------------------------------Trinn2
    Pressure = ActiveSheet.Range("C2")
    Temperature = ActiveSheet.Range("C3")

    With Ark2.QueryTables.Add("URL;http://www.peacesoftware.de/einigewerte/calc_co2.php5", Ark2.Range("A1"))
    .PostText = "lang=english&calc=standard&druck=" & Pressure & "&druckunit=1&temperatur=" & Temperature & "&tempunit=1&Submit=Calculate"
    .RefreshStyle = xlOverwriteCells
    .SaveData = True
    .Refresh
    End With





    End Sub

    Sub GetData2()
    '------------------------------------------Trinn4
    Pressure = ActiveSheet.Range("E2")
    Temperature = ActiveSheet.Range("E3")

    With Ark6.QueryTables.Add("URL;http://www.peacesoftware.de/einigewerte/calc_co2.php5", Ark6.Range("A1"))
    .PostText = "lang=english&calc=standard&druck=" & Pressure & "&druckunit=1&temperatur=" & Temperature & "&tempunit=1&Submit=Calculate"
    .RefreshStyle = xlOverwriteCells
    .SaveData = True
    .Refresh
    End With
    End Sub

  5. #5
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,239

    Re: VBA - how to send POST data to a web form and get results into worksheet

    You don't need to keep adding new tables, just check if it exists and use it if it does:
    Sub GetData(SheetName As String, Pressure As Double, temperature As Double)
    Dim qt As QueryTable
    
    If Sheets(SheetName).QueryTables.Count <> 0 Then
        Set qt = Sheets(SheetName).QueryTables(1)
    Else
        Set qt = Sheets(SheetName).QueryTables.Add("URL;http://www.peacesoftware.de/einigewerte/calc_co2.php5", Sheets(SheetName).Range("A1"))
    End If
    
    With qt
        .PostText = "lang=english&calc=standard&druck=" & Pressure & "&druckunit=1&temperatur=" & temperature & "&tempunit=1&Submit=Calculate"
        .RefreshStyle = xlOverwriteCells
        .SaveData = True
        .Refresh
    End With
    
    End Sub
    Sub kyl()
    
    GetData "sheet2", 20, 30
    End Sub

  6. #6
    Registered User
    Join Date
    01-05-2012
    Location
    Stavanger
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: VBA - how to send POST data to a web form and get results into worksheet

    Hello, and thank you for reply, this works much better!

    Is there any way to add data in new rows in another workbook using VBA?
    F.eks. i have data in book1 in cell A1 and A2, and i want to add them to book2 cell B1 and B2.

  7. #7
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,239

    Re: VBA - how to send POST data to a web form and get results into worksheet

    Something like:

    Sub exportData(wbName As String)
    'wbName should inlcude the full filepath and the workbookname
        With GetObject(wbName)
            With Sheet1
                .Range("B1").Value = ThisWorkbook.Sheets("sheet1").Range("A1").Value
                .Range("B2").Value = ThisWorkbook.Sheets("sheet1").Range("A2").Value
            End With
            .Close True
        End With
        
    End Sub
    Last edited by Kyle123; 01-12-2012 at 10:19 AM.

  8. #8
    Registered User
    Join Date
    07-31-2012
    Location
    Southern California
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: VBA - how to send POST data to a web form and get results into worksheet

    I am new to the board. This code is so useful! How would I apply this code to the website below?
    I want excel to enter in the form Permit Type = 1001 and Permit Number = 20120001 and then
    return the results in Excel. I spent 4 hours looking at the source code of the website and attempted
    it below, but so far no results. I really want to understand the html I need to look at to make this
    query happen. Thank you so much.

    Sub GetData3()

    Dim per_type As Double
    Dim per_nbr As Double

    my_per_type = 1001
    my_per_nbr = 20120001

    With Sheet1.QueryTables.Add("URL;http://landinfo.sdcounty.ca.gov/permit/lookup/index.cfm?fa=dsplprmt", Sheet1.Range("A1"))
    .PostText = "per_type=my_per_type&per_nbr=my_per_nbr&Submit=Search"
    .RefreshStyle = xlOverwriteCells
    .SaveData = True
    .Refresh
    End With

    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1