Updating Internet Web page from excel

[apache51ster]

Hi,

I've a spreadsheet that is used to update a current website. The data is manually entered by doing copy & paste multiple times. I need to automate this process by an excel macro which will automatically update the web forms on the basis of the excel data. What we update is a data like name & address but we need to search the employee with his/her employee ID & then update his/her name & address. So the first action we take is copying the emp id from the excel & entering the same in the website & conducting a search. Based upon the search results, we will update the address & name information.

What I mean is updating a web form with the data in excel. Sorry for the confusion.

[SamT]

The answer really depends on the web site and how it is set up. If it, or at least the parts you need to update were originally published with Excel or any of the MS Office products, it's pretty straight forward.

I'm trying to picture your Data Structure:

• Emp Names and IDs are stored in Excel
• The Emp ID and Address are stored on the Web site.
• End Data Structure

The process is:

• Search XL for Name, retrieve ID,
• Search Web for ID, retrieve Address
• Update Web form with new Name and Address.
• End Process

Obviously, that is not correct, so update me.

[apache51ster]

Hi Sam,

Yes you are almost correct. Just that there is a change in the process which will be -

The process is:
XL will have a list of ID, Name & Address which should be updated in the website.
The unique reference is our ID so we will search the website to retrieve the employee account to update the name & address.
Search Web for ID, retrieve account
Update Web form with new Name and Address.
End Process

[SamT]

Ok, Sister, got it now.

But now I have another question. Is the "Account" you retrieve a static web page w/ form or is your site database driven with PHP/MySQL or something similar?

SamT,
Your Choctaw cousin.

[apache51ster]

Hi,

It is a database driven with PHP/MySQL.

Thanks again.

[SamT]

The following codes are just an outline. You will need to get with your Site admin and your DB admin to get all the needed info to connect to the DB and build Queries and Puts for the DB.

[VBA]'Uses Microsoft ActiveX Data Objects 2.8 Library

Dim oConn As ADODB.Connection

'Check with Website Admin for fol;owing values
Const Database_Name As String = "Your DBName Here"
Const Server_Name As String = "Your Server Name.WebSite"
Const User_ID As String = "UserID For DB"
Const DB_Password As String = "Password for DB"[/VBA]
[VBA]Sub Trigger()
'You must decide how you want to Trigger the process,
'Command Button, Selectiion Change, Workbook Open, Etc.

'This is an example of the process flow

Dim Cnxn_Name As Boolean
Dim Success As Boolean

Cnxn_Name = Open_Connection
If Cnxn_Name = "Fail" Then Exit Sub

Success = Transfer_Data
If Success Then
'Do More Work
Else
'Blah, blah, blah
End If

Success = Close_Connection

End Sub[/VBA]
[VBA]Function Open_Connection() As Variant
'Returns True if New Connection is successfully opened
Set oConn = New ADODB.Connection
oConn.Open "DRIVER={MySQL ODBC 5.1 Driver}; " & _
"SERVER=" & Server_Name & "; " & _
"DATABASE=" & Database_Name & "; " & _
"USER=" & User_ID & "; " & _
"PASSWORD=" & DB_Password & "; " & _
"Option=" & "Unknown to SamT"

If "No Problem" Then
Open_Connection = "Connection ID"
Else
OpenConnection = "Fail"
End If
End Function[/VBA]
[VBA]Function Transfer_Data(Cnxn_Name As String) As Boolean
'To be Written

Dim Table_Name As String
Dim Field_Names As String 's)
'Dim Other stuff you'll need to query the DB

'Calls other functions that do all the work
'Each Returns True on success

With Cnxn_Name
'Do Stuff
End With
Transfer_Data = True 'or false as the case may be

End Function[/VBA]
[VBA]Sub Close_Connection()
'To Be Written
End Sub[/VBA]

I hardly spent any time at all on this, so don't expect it to be any more that a teaching/learning aid.