2

Good day,

I have 2 issues I am trying to workout on some SharePoint Lists. I was hoping I could get a little help as my developer skills are just now developing beyond OTB solutions.

Issue 1: I have seen a few fixes on this through my searches, however most of what I find have been half answers. I have a SharePoint list I have exported to excel. This list has a few lookup columns to pull names from our client and personnel directories. It also pulls what appears to be the Record ID along with the value into the excel. I cannot have these export along side the names.

Example: J. Rigotti;#60 J. Shamieh;#131 S. Dearborn;#116

Issue 2: With the same list above, I am having issues getting the columns to export in the proper order in the excel. They are all correct within the browser exactly how I need them. However on the excel they are a mess. Can I organize the excel columns prior to the export?

Improve this question

2 Answers 2

Reset to default
0

Issue 1: That isn't a mistake, that is how the records are stored in Lookup columns. They 'Will' export with everything else, just modify the field they go to split it out after the fact. Cheap trick for those that insist on Excel.

You might try to create a calculated column that splits on the ;# for you and check how it downloads.

Issue 2: My understanding is that Excel uses whatever the 'Default' view is. Make sure that your Excel View is also the one set as the Default for the list.

Future Issue 3: These issues are not uncommon and get worse the more complex the lists are. Surveys in particular beat up excel pretty bad. There are multiple ways to pull data from SharePoint into Excel outside of just 'Export to Excel'. One of the easiest things is to simply start educating your users in On Demand reporting. To me that means, they look at the same page their customer looks at. Code handles it all so there's no exporting to excel in the first place.

Improve this answer
3
  • On Issue 2, it is exporting other columns that are not part of the view. Similar to how it exports Path. I get Path will always be there, but these are fields I created.
    – Cory Cox
    Commented Feb 23, 2017 at 20:04
  • I'll double check something. Are you on SharePoint 2010 or 2013, or which?
    – Jack
    Commented Feb 23, 2017 at 21:36
  • SharePoint 2013, Run by GE so its locked down hard. I have access to do most things and direct support from the development team if needed.
    – Cory Cox
    Commented Feb 24, 2017 at 1:05
0

I have solved both of these issues. Issue 1: Ran a macro recorder and did a find and replace all

Sub RemoveNumbersFromNames() ' ' RemoveNumbersFromNames Macro ' Removes the (;#number) from the names in sheet '

' Cells.Replace What:=";#*", Replacement:=" ", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub

Issue 2:

I ran a script in the background to create a new worksheet and pull/copy/paste the columns I want to have in the order I needed them in.

Public Sub ReorderCellsForMasterTracker() Set range1 = Range("A:L, O:AA") range1.Copy Set NewSheet = Worksheets.Add ActiveSheet.Name = "Master Tracker" ActiveCell.PasteSpecial Paste:=xlPasteValues Range("A1").EntireColumn.AutoFit Worksheets("Master Tracker").Columns("A:Z").AutoFit Call RemoveNumbersFromNames Call ChangeColors End Sub

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.