Data changed when imported

[john3j]

Hey guys,

I have a MS Database that is linked to tables in sharepoint. In Sharepoint I have about 30 people whose names are reverted to numbers when pulled into Access, and Excel. Each person is directly related to an ID number, and that is what is being displayed in Excel, instead of the persons name. I dont know why or where the data is being reverted to the persons ID number through the process, but I need the names to display. I had an idea to create a select case that would do the following:

Find the last row in the sheet to set the range.
Go through three specified Columns, we will say F, G, and AB
From there change the text to the persons name depending on the ID number diplayed in each cell of each column.

I dont know if this is the best way to fix this problem, but if anyone has any ideas, please let me know. Also if anyone could help me write the code for the select case it would be great!

Thanks,
John:dunno

[Kenneth Hobs]

You must be importing the id field rather than a name field. We would need the database or the sheet and column where the lookup for ID's exist and the matching Names sheet and column. A short example xls would help us help you.

[john3j]

Ok after some research it looks like there is a compatibility issue with Sharepoint 07 and Office 2003 or we havent gotten a patch yet. I have a list of Id numbers matched up with the names of people. If it were only 10 people or so I would just do a select case. Here is the kicker though, I have over 400! I would like to put an index sheet in the workbook and somehow match id numbers with what is in this so called index and automatically change it to what its supposed to be. Please look at the attached spreadsheet, its just a simple example. Thanks!

[Kenneth Hobs]

Put this in a Module. Select your ids to lookup and play the macro.
Sub FindReplace()
Dim objFind As Range
Dim objSheet As Worksheet
Dim strRange As String
Dim cell As Range

strRange = "B:B"
Set objSheet = Worksheets(".xlsx]Legend")

For Each cell In Selection
Set objFind = objSheet.Range(strRange).Find(what:=cell.Value, _
LookIn:=xlFormulas, lookat:=xlWhole, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False)
If Not objFind Is Nothing Then cell.Value = objFind.Offset(0, -1).Value
Next cell
End Sub

[john3j]

What do you mean select your ids to lookup and play the macro? I was looking to just call the module when the workbook is opened. I tried doing this but it wouldnt work.

[Kenneth Hobs]

If it doesn't work manually then doing it in an open event makes no sense. So, does it work manually? If so, you still use it as designed. Your open event would select the ids and then Call the macro.

I designed it for Selection. You can always code it to work on a passed Range or a dynamic named range.

[john3j]

I am sorry for the confusion. I wanted to be able to open the workbook and it automatically go thru and change all of the id numbers to the names. It would be nice if it worked automatically.

[Kenneth Hobs]

So, what is the problem? It just takes a few additions to do that with my code. Select your id cells and then run my macro.

If it did what you wanted, then we can show you how to put it in the Open event. I would have to know the sheet name and the first id's cell address to set the Selection range.

e.g. If id cells are in "Sheet1" from A2 to the last cell in column A.
Change Selection to:
Worksheets("Sheet1").Range("A2", Worksheets("Sheet1").Range("A" & rows.count).end(xlup))