VBA to Write to Website

[ptw78]

Does anyone if you can have vba in Access(or excel) write to a website? eg. Take data from say a form in Access and write it to particular form fields on a website. Or the same for Excel. If so can someone post an example or point me in the direction of how to start off on that? Thanks

 

[PaulBricker]

http://www.w3schools.com/asp/default.asp

Start here at the w3schools website and look at the ASP tutorial. To send information to your website from an access database, you need to use a server side language. ASP or PHP are the two that come to mind first, but there are lots of them out there. Depending on your particular circumstances, ASP is likely the place to start. Within the ASP frameword, you can use multiple vba type languages to do what you want.

Paul

 

[ptw78]

I don't think this is what I'm looking for. Not even sure you can do what I'm asking. Basically what I'm looking to do is something like write a macro in excel vba run that macro that will take data from the excel sheet(s) and place it onto a webpage. So the coding will still be in excel via vba.

 

[JoeAtWork]

To send information to your website from an access database, you need to use a server side language. ASP or PHP are the two that come to mind first, but there are lots of them out there.

Umm, that's not right at all. Server-side languages are processed on the web server and are rendered into HTML for the client browser. ASP or PHP is meaningless on the client side, which is where the OP's program is sitting.

I've done this years ago using the Visual Basic embedded browser control. You basically use the Document Object Model (DOM) to enter the text into the correct boxes, click buttons, etc. There's probably better ways of doing this these days, but its been a long time since I've had to do this, so I can't tell you much more.

 

[jsteph]

ptw,
Can you give us some context? Ie., I assume the website you speak of is under your control, ie, your company or you host it or it is hosted by a third party but you have admin access to this site?

If you want to use the vba language to do what you wish--but now on a website and not an Excel sheet or Acces form--hen ASP is very, very similar to VBA.

So what I'm getting at is that if your concern is that you have an existing skill or codebase in VBA, and want to use that existing logic but now on the web instead of Access or Excel--but still using Access or Excel data stores, then ASP is ideal for that. You can reference any odbc source (including Acces and Excel) and use almost the same code to do the logic.

The difference is in the input and output methods to the screen. For example in Access to dislpay a datasheet view of a table's contents you just put it in a subform, in ASP you do responose.write and create an html table on the fly.
--Jim

 

[ptw78]

The specific sites are on an internal intranet so I cant give you an example of it. But here is maybe a better explanation of what I'm wanting to do. What I do now with VBA is code in excel or access module to read/write to EXTRA Mainframe. I'm wanting to do the same thing(mainly just write) to website. So for example say I have some data in excel. I want to write the vba code in excel, run the code to write the data onto a website. So below would be an example of some code I would use in Extra that I would code in excel and it would write to Extra. If I wanted to do something similar but instead of it writing in Extra it would write to a website instead.

Sub Main()

Dim Sessions, System As Object, Sess0 As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession
        file = "H:\Macros - Reports\FC Refferals Macros\120 Macro\120 Macro (4)\120 Macro Input 4.xls"
        Dim obj As Object
        Dim objWorkbook As Object
        Set obj = CreateObject("Excel.Application")
        obj.Visible = True
        obj.Workbooks.Open file
  Dim ws As Worksheet
   Set ws = ActiveWorkbook.Sheets("Sheet1")



    'Do loop starting at row 2 and ending at Worksheet.UsedRange.Rows.Count
    'which is end of all rows with data in the sheet.
    For x = 2 To ws.UsedRange.Rows.Count
    
    'Set account number = to row (row x, column 1), where x = 2 for the first loop and going
    'all the way to the end of the count of rows, so on the second loop, x would be 3, so
    'it would read the account from row 3, column 1.  It starts at 2 because it assumes there
    'is a header row on the worksheet, you can change it in the above for statement
    str_act_num = ws.Cells(x, 1)
  

  'moves cursor to location 7,30
    Sess0.Screen.MoveTo 7, 30
    

    
    'send acct num string and erases end and enters
    Sess0.Screen.SendKeys ("x")
    Sess0.Screen.MoveTo 10, 21
    Sess0.Screen.SendKeys (str_act_num & "<EraseEOF><Enter>")
    Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
    
    
    
    If Sess0.Screen.GetString(23, 2, 17) = "ACCOUNT NOT FOUND" Then
        
        str_status = "ACCOUNT NOT FOUND"
     
        Else
        
        str_status = "ACCOUNT FOUND"
            

       str_reason = Sess0.Screen.GetString(21, 26, 8)
       ws.Cells(x, 4) = str_reason
       
       str_reason = Sess0.Screen.GetString(21, 44, 1)
       ws.Cells(x, 2) = str_reason
       
       str_reason = Sess0.Screen.GetString(4, 16, 8)
       ws.Cells(x, 5) = str_reason
       
       str_reason = Sess0.Screen.GetString(12, 55, 13)
       ws.Cells(x, 6) = str_reason
        
       str_reason = Sess0.Screen.GetString(12, 14, 3)
       ws.Cells(x, 7) = str_reason
      
       
       Sess0.Screen.SendKeys ("<pf3>")
       Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
       
       
       End If
 
  
   ws.Cells(x, 3) = str_status
   
Next x
 
    MsgBox "Macro Done"
    
End Sub

 

[JoeAtWork]

From your original request:

Take data from say a form in Access and write it to particular form fields on a website.

From that my understanding is that there is a website with text fields you need to fill in (something a user would type into) and then probably press some sort of Submit button.

From your last post:

I want to write the vba code in excel, run the code to write the data onto a website.

From that it seems you may want to actually insert the data into a rendered HTML page, in which case ASP is indeed an option.

So what is it you actually want to do:
1. Go to a web page and fill in existing controls
2. Generate the web page with the data inside the HTML

??

 

[ptw78]

Correct

 

[JoeAtWork]

My question doesn't have a Yes or No answer, as the two options are essentially opposites.

 

[ptw78]

What i'm looking to do is go to a web page and fill in the existing fields click submit with the data on my excel or access file.

 

[JoeAtWork]

OK, then it is option 1 and essentially my first answer. ASP won't be the answer for you.

 

[ptw78]

Yes, what I'm wanting to know is there a way to do it in VBA?

 

[ptw78]

anyone able to assist w/this?

 

[jsteph]

I'm still not sure exactly what's needed here...

Questions for ptw78:

If this is an Internal Intranet site, then presumabley it's under your control?

If the answer is Yes, then why can't you "eliminate the middleman" and not use the website at all?

Are the following two assumptions close to correct:
1: I'm assuming the purpose of this website is to gather data and put it in a databasse or output some sort of report?

2: And the "source" of this data is data you already have in Access or Excel?

So if those are correct, why the website? Why not just use VBA to move the data to the final destination database or report?

If, on the other hand, the final destination *is* the website, and the source data is Excel or Access, then we're back to my original suggestion that ASP code will take any ODBC data and generate html for your site dynamically.

I'm not sure if it's me but I just never got a real clear idea of what the requirements were here.
--Jim

 

[kjv1611]

ptw78,

You need to try to be more clear about what you really want to do. If for whatever reason, you WANT or HAVE to send the data to the controls (text boxes, etc) on a website, then that can be done, but it doesn't seem the best way. That's what I think most everyone here is trying to get at.

If you just absolutely have to send it via the user interface on the website, you can use SendKeys from VBA to send to any other source. Look up SendKeys in the VBA HelpFile, and if you want a better SendKeys implementation, then look here:

http://www.mvps.org/access/api/api0046.htm

Otherwise, what is the website writing data to? There's surely a database that it's sending data to, and if you're using MS Office and VBA normally, then there's at least a good possibility that you're trying to feed data (eventually) to a SQL Server. If so, there are plenty of better ways to get the data to the server - and that is how you should go about it if at all possibe.

 

[JMANTN]

ptw78, Did you ever get a response to this? I too am trying to take data from an access table and via vba and Microsoft Internet Controls/Microsoft HTML Object Library trying to paste into a text box on a website I do not have full control over and am trying to avoid the use of sendkeys or sendmessage.

I have the correct control ID's as I can have vba put specified text from within vba into the text box but cannot get it to pull my table and paste the contents.

I'm using access 2007 on xp with my company's IE 6.0 and if I find the solution I'll post if no one else comes up with a solution

 

[ptw78]

No solution yet. I'm not even as far as you are using the Internet controls & HTML library

 

[JoeAtWork]

I have the correct control ID's as I can have vba put specified text from within vba into the text box but cannot get it to pull my table and paste the contents.

It sounds like the only thing you don't know is how to query the database. That's very generic run-of-the-mill code, e.g.

Public Sub GetDataFromMe()
    Dim rs As ADODB.Recordset
    
    Set rs = New ADODB.Recordset
    
    rs.Open "SELECT * FROM Codes", CurrentProject.Connection, adOpenForwardOnly, _
     adLockReadOnly, adCmdText
    
    Do While Not rs.EOF
        '**** Do whatever you need to with the data ****
        Debug.Print rs("Code")
        rs.MoveNext
    Loop
    
    rs.Close
    
End Sub

 

[JMANTN]

JoeAtWork, I appreciate the quick response! It definitely gave me some more ideas as it didn't work 100% and since I'm new to VBA (halfway through the dummies book right now for vba for access)and while some of it is coming to me pretty fast, I still struggle with things I've never dealt with previously.

ptw78, I didn't want to completely hijack your thread so I started my own but wanted to link to it in case it can help you.

http://www.tek-tips.com/viewthread.cfm?qid=1602641&page=1

 

[JMANTN]

ptw78,

Part of your issue may be incorrect element id's if you're still struggling with it. I found this code

http://www.tek-tips.com/faqs.cfm?fid=6399

invaluable. Just copy the code into an excel document's vba and go to the developer tab (if running 07) and hit macros and assuming you have the webpage pulled up currently select the Getfields (I think) Macro and it makes finding the names of the elements a lot easier.