Our company website (intranet) has a phonebook page that you can search for other people in the company, it is missing the option to auto import into Outlook which means that you have to copy and paste name, phone numbers, email address etc. into a new contact in outlook, which is a bit time consuming.
I have been trying to make this a bit more automatic by the following:
1. open the relevant contact in the phonebook and copy the web address
2. Paste the web address into the excel file - worksheet 1
3. Run a macro which will do the following actions:
A. Using the web address carry out a web query on that contacts page and put the results in worksheet 2
B. Using the contact details in worksheet 2 copy and paste the information into worksheet 3 using with standard outlook header fields for importing contacts.
C. Delete worksheets 1 & 2 and save as a CSV file ready to be imported into Outlook in a certain folder
[In outlook then I would either manually import a contact or make a Macro to auto import from a specific file in a specific folder]
Points 1, 2, 3b and 3c I can do but I am stuck on 3a – I can’t automate the Macro to use the address pasted into the Excel file, I can get it all to work but each time I want to change the web address I need to edit the macro* – which slows down the whole process.
I am looking for help on how to get a Macro to use the text in the sheet, any help would be great I have searched everywhere but I think that the terms I am using to search with are wrong and I am turning up nothing.
John
* I am using the following web query code which I got from this site: http://support.microsoft.com/kb/187364 (the rest is all pretty basic VBA stuff).
Sub URL_Get_Query()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://xe.com", _
Destination:=Range("a1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub
I have been trying to make this a bit more automatic by the following:
1. open the relevant contact in the phonebook and copy the web address
2. Paste the web address into the excel file - worksheet 1
3. Run a macro which will do the following actions:
A. Using the web address carry out a web query on that contacts page and put the results in worksheet 2
B. Using the contact details in worksheet 2 copy and paste the information into worksheet 3 using with standard outlook header fields for importing contacts.
C. Delete worksheets 1 & 2 and save as a CSV file ready to be imported into Outlook in a certain folder
[In outlook then I would either manually import a contact or make a Macro to auto import from a specific file in a specific folder]
Points 1, 2, 3b and 3c I can do but I am stuck on 3a – I can’t automate the Macro to use the address pasted into the Excel file, I can get it all to work but each time I want to change the web address I need to edit the macro* – which slows down the whole process.
I am looking for help on how to get a Macro to use the text in the sheet, any help would be great I have searched everywhere but I think that the terms I am using to search with are wrong and I am turning up nothing.
John
* I am using the following web query code which I got from this site: http://support.microsoft.com/kb/187364 (the rest is all pretty basic VBA stuff).
Sub URL_Get_Query()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://xe.com", _
Destination:=Range("a1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub
