Integrating A SharePoint List

Oct 13, 2009

From a SharePoint list I need two important columns in Excel (there are more columns in the list..). These colums contain a number and a single line of text belonging to that number. Many users have their Excel sheets with this information. Unfortunately the users maintain these lists manual. I know I can export a list to Excel, but I want to integrate the data in existing list. Users are forced to build their excel sheets in the way a SharePoint list is build, so the data can be integrated. When a new item is added/deleted/changed in the list, the excel will automatically update. Is there a way to realise this? For example with an extra monitoring column which says which line is updated, deleted etc.

View 10 Replies


ADVERTISEMENT

Excel Cannot Refresh Sharepoint List Anymore?

Jun 1, 2014

At work we use a connection to a SharePoint List within Excel to refresh the data in the files.

When excel is refreshed I get this error (this happens for all users in the team and multiple workbooks)

Image 1.png

The weird thing is I can export the SharePoint list to an excel file and it is fine but as soon as i go to refresh it (Data>Refresh All) it produces the above error.

View 1 Replies View Related

Excel 2010 :: Macro To Upload SharePoint Project List Then Copy To Existing Workbook?

Jul 11, 2014

I need to be able to export a project list to Excel and overwrite the contents in an existing Summary Report with the exported list. Is this possible? I am using SharePoint 2013 and Excel 2010.

View 4 Replies View Related

Integrating Data

May 6, 2008

I have a master list of about 500 folks, each with their own unique identifying number.

I want to search another list of names all of who appear on the master list, the new list has the same unique identiyfing number to match with the master list. The new list has data I want associated with the name on the master list in a specific cell/row.

I have tried vlookup, if, lookup etc. I need it so that if a name on the master list does not appear on the new list in lieu of the "false" I need it to place a code of three text letters. If the name does appear it will deposit a number between 0 and 5.

View 9 Replies View Related

Integrating VBA Userform

May 30, 2006

I tried to build a userform in VBA and with the help of a few threads on this forum I even tried to start putting some data. Needless to say I'm at a loss. I'm hoping to get the userform to permanently display in sheet 1 with it populating the corresponding columns in sheet 2 with data on pressing the submit button. Once populated, I will use basic formula's in sheet 2 to generate some sales figures.

View 8 Replies View Related

Integrating Two Nearly-identical Codes Into One With Visual Basic Editor

Aug 6, 2014

I'm having difficulty integrating two nearly-identical codes into one with Visual Basic Editor. Here is the coding that is leading me to trouble.

[Code] .....

View 2 Replies View Related

Check In/ Out Excel Doc On Sharepoint

May 25, 2011

I have a doc on sharepoint called adamc.xlsx

When I open the doc I would like the workbook open event to check the document out for editing on the workbook close event i would like it to be checked back in...

Been playing around with Workbooks.CancheckOut but I've completely lost the plot.....

View 9 Replies View Related

Export Excel To Sharepoint

Dec 3, 2012

1) I have a excel on a sharepoint, i want to link it to the other sharepoint. Can this possible.

2) I want to open a excel which is in sharepoint, and export it to network place created in my computer. then save

View 2 Replies View Related

VBA To Uploads Multiple Files To Sharepoint?

Aug 18, 2011

I would like to find out the excel VBA code to copy files to a sharepoint directory ?

View 5 Replies View Related

How To Upload A File To Sharepoint 2010

Dec 5, 2012

I need to upload the excel file (Book1.xlsx) which is placed in my desktop to the sharepoint 2010.

View 1 Replies View Related

Publish Data To Sharepoint Calendar

Nov 16, 2007

I use an excel report that connects to an external sql datasource and using macros imports and arranges the data showing dates, job number, description and author of various jobs coming up at work.

I want to publish this spreadsheet's contents onto a calendar in excel or (preferably) onto a sharepoint calendar.

Does anyone know how to:

1) Publish the spreadsheets contents into a calendar in excel?

2) Publish the spreadsheets contents onto a calendar in sharepoint?

View 3 Replies View Related

Excel 2007 :: Search For A File In Sharepoint Using VBA

Apr 18, 2014

I want to search for a file in Sharepoint using Excel 2007 VBA.

Path to the Sharepoint location where the files are at is [URL] ....

File name is customer_list_xxxxxx.xlsx The x's are a date which changes every day or week. So a new file will be uploaded to the sharepoint path.

Example customer_list_041414.xls

I've tried so many different code options for this, but no luck.

View 1 Replies View Related

Checking In Document In SharePoint From Excel Macros

Jun 24, 2014

We have mapped a network drive to a SharePoint Directory, while we are able to copy a file (using Macros) to this location, this file does not appear in SharePoint to the other users, the reason being that it is not checked in. How to Check using Excel Macros.

[URL] .....

View 1 Replies View Related

Rearranging Exported SharePoint Lists In Excel?

Jul 1, 2013

I've just exported a list from SharePoint to Excel, which gave me as a result a owssvr table, so far so good cause every time the list in SP is updated I can do a refresh and get the latest values; however once I opened the table I realized that the columns where not correctly order they were all mixed and the information does not look as it is required so I need to rearrange them. I do not need to delete a column I just need to be able to move them between themselves so that they will follow certain order: Product ID, Name, Amount of pieces, Place where they are stored, etc. this is very important because later on I use "vlookups" to do a series of reports.

I've tried cut- paste to move the columns to the correct position, unfortunately once I close the excel file and try to open it again, I got a message saying that the content is not readable and when Excel repairs it, my owssvr table loses the link to SP so I cannot update refresh the table anymore.

I cannot edit the list in SP as this site does not belong to me and I only got access to export the data and be able to refresh the table, all I want is to be able to move them within my ovssvr table so that locally I can work with them better.

Here is a pic of what I am talking about: cmms.JPG

So for example in the pic I put, I need that instead of Comments in Column E, Product ID can be in Column E, then Name in column F and so on..

View 1 Replies View Related

How To Traverse Folders And Files On SharePoint With Excel VBA

Mar 9, 2011

how to traverse all files in SharePoint folders using Excel VBA and have not come across an answer I could use because, by configuration design, I cannot map a SharePoint URL to a drive letter.

Here it is:

Sub SPDir()
Dim wb As Workbook
Dim dummyFile As String
'
' The file specified by dummyFile must reside in SharePoint in order to use SharedWorkspace
' The way the code is set up, the path and filename in dummyFile should NOT substitute %20 instead of spaces
' There is logic later to ignore dummyFile on output
' Substitute your own dummy file name below

[code].....

View 9 Replies View Related

VBA To Query SharePoint Document / Folder Permissions

Jan 4, 2012

I'm using VBA to query file information (path, name, size, dates, etc.) on files on a SharePoint site and returns them to Excel. I'm curious if also returning the document's permissions is possible. If not for the document, can one programmatically get the folder's permissions?

View 6 Replies View Related

VBA To Import Access Database Stored On SharePoint

Jun 14, 2013

How to import data from an access database, specific tables, to excel using vba; the trick being the database is stored on SharePoint Server 2010.

View 2 Replies View Related

Macro To Copy Data From One To Another Sharepoint File

Apr 16, 2014

I created a macro on an excel file on my local PC that opens another excel file on my local PC and copies some data from file 2 and pastes that data in file 1. In file 1 have a Path and File Name that the Macro uses to get Open File 2 from. This works Great on my local PC!! However, I need to move this to a SharePoint. When I did I tried to change the Path and File name to the ones from the URL generated by SharePoint Send Link. No Luck, then tried to change the 20% replace for spaces. Still no luck. Do I need to use some other method of opening a file that is on SharePoint vs Local PC?

Currently have I have:

Sheets("Labels").Select
Path = Range("b2").Value
BenFile = Range("b3").Value
InterOpFile = Range("b4").Value
NMOCFile = Range("b5").Value
SECFile = Range("b6").Value
VistaAFile = Range("b7").Value

and Later in the Code:

Workbooks.Open Filename:=Path & BenFile

View 1 Replies View Related

Macro (VBA) To Save File To Web Directory (SharePoint)

Jun 17, 2014

I have recorded the below code that functions fine from my computer. My issue is that I need to export/import this macro to other workstations who may have the save to location mapped to a different drive letter. Is there a way to replace the drive letter with the URL (SharePoint)?

ChDir "E:3. CRQsRemedy Dumps"
ActiveWorkbook.SaveAs Filename:="E:3. CRQsRemedy DumpsToday_CRQ_8Dump.xls" _
, FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Save To Directory: \server_1Dir_1Dir_2FolderToday_CRQ_8Dump.xls
Permissions are in place.

View 1 Replies View Related

Checking In Document In SharePoint From Excel Macros

Jun 24, 2014

We have mapped a network drive to a SharePoint Directory, while we are able to copy a file (using Macros) to this location, this file does not appear in SharePoint to the other users, the reason being that it is not checked in.

How to Check using Excel Macros.

View 2 Replies View Related

Conditional Formatting With Sharepoint Data Connection

May 30, 2007

I have an Excel workbook that has a data connection to a Sharepoint List. I have 4 columns showing the quaterly status of a project. I want to apply conditional formatting based on the value in the column. I have 5 possible choices so I have to use VBA code to accomplish this because of the limit of 3 option in Excel. I am using this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Intersect(Target, Range("X:AA"))
If rng Is Nothing Then
Exit Sub
Else
Dim cl As Range
For Each cl In rng
Select Case cl.Text
Case "1"
cl.Interior.ColorIndex = 4
Case "2"
cl.Interior.ColorIndex = 6
Case "3"
cl.Interior.ColorIndex = 3
Case "4"...............................

View 3 Replies View Related

Excel 2007 :: SharePoint / Checking File Status

Jun 11, 2014

I'm working with a file from SharePoint (FileA), "FileA" goes out to Sharepoint and opens "FileB" (if not already open) then copies information from "FileA" to "FileB". However if "FileA" is left open, or someone else has it open I am unable to open "FileA". How in VBA can I check to ensure I don't have it open locally (if so swich and continue), checkout and open in SharePoint (if not already open) or Prompt with options to force checkin, checkout to copy info?

Simple VBA copy I used:

Workbooks.Open FileName:= _
"http://sharepoint.baaa.com/sites/SCDT_DSD/Weekly_Releases/11.%20Combined%20Weekly%20Task%20List/1/Automated_Status_Update.xlsm"
Sheets("1").Select
Range("A1").PasteSpecial Paste:=xlPasteValues

View 2 Replies View Related

Exclude Network Path In PivotTable Based On Sharepoint

Sep 4, 2007

I have an excel file linked to a sharepoint 2007 network space. Everytime I open the file, my pivot tables will not refresh, because the source of the pivottable seems to update automatically to include the network drive name on them which somehow causes a problem. Is there anyway to solve this? I put a snippet of the source in the pivottable as an example.

"http ... /PMO/Shared%20Documents/ Finance/Budget/2008/2008%20What%20If%20Budget.xls'!=WhatIf"

View 3 Replies View Related

Running Code On SharePoint Linked Worksheet To Update Existing And Future Data

Aug 8, 2012

I inherited a spreadsheet to manage that is linked to a SharePoint table.

It is trying to populate a date that a certain "Tier" is selected (1, 2, 3, or 4).

It works great if I manually type in the tiers, but does not run on existing data (about 400 records) or lines that are updated and new via the SharePoint list.

How can I have this run on all of the existing lines and anything added or changed in the future from the list?

VB:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("AD2:AD10000")) Is Nothing Then
Application.EnableEvents = False

[Code] ....

View 9 Replies View Related

Excel 2007 :: Generate Single Sheet Workbook And Save To Sharepoint Site

Jan 4, 2012

Automating Excel from Access, I'm attempting to generate a single sheet workbook and save to a Sharepoint site. This process works fine on my machine (XL2007, XP), but on a coworkers computer (XL2003, XP), the code throws a 1004 error on the save as line. However, the really odd thing is that stepping through the code doesn't throw an error on the coworkers machine.

Here's the sub being ran; the line it errors out on is the first branch in the .saveas block.

Code:
Private Const csSharePointSaveAs = "\sharepoint-us.mycompany.comsitesfinance adminlah blahlah-blahCustomer Publication Tracking.xls"

Private Sub PublishXLtoMOSS()
Dim objXL As Excel.Application 'Object
Dim wb As Excel.workbook 'Object
Dim ws As Excel.Worksheet 'Object
Dim rs As DAO.Recordset
Dim i As Long

'Set objXL = CreateObject("Excel.Application")

[Code] ...........

View 1 Replies View Related

Excel 2010 :: Workbook Not Closing Correctly When Accessed Via Link On Sharepoint Site

Feb 18, 2013

I was asked to create a userform for the use of our whole team which would enable them to select a name from a dropdown list and then it would display their business contact details in the rest of the form.

I'm running Office 2010 and Windows XP server/enterprise edition (not quite sure which). After some research I've managed to use VBA code to open the userform while hiding the workbook, then when the close button is clicked it closes the user form and it's associated workbook using the code below.

Code:
' ThisWorkbook module
Private Sub Workbook_Open()
Application.Visible = False
UserForm1.Show
End Sub
'UserForm module
Private Sub CommandButton1_Click()
Unload UserForm1
ThisWorkbook.Close (False)
End Sub

This works great when the excel file is accessed directly. However when the excel file is accessed via the link on my teams Sharepoint site it doesn't. When the Userform is closed the underlying workbook and data is then displayed which then has to be closed separately.

View 2 Replies View Related

List 3 Different Values To List Of List Of 3 In Combobox?

Jan 12, 2014

how I can list 3 different values to a list of list of 3 in a combo box, is.

combo box

select high = 35
select middle 30
select low = 25

I have the names in the box I just need it to add a value (which I have listed 1 in each separate cells) to each selection

View 9 Replies View Related

Make Unique List From List Without Duplicates But Hold Cell And Pull Corresponding Dates

Apr 18, 2013

I have my dummy data, and I have (what I think) is how I want the data to be shown. My friend uses Google Sheets, but I prefer Excel. I am trying to convert the code because I am a stickler for excel. Typically I can convert codes some easily, but this is way beyond me.

For Column A: I want to create a list on sheet 'Setup!' based on ids!D2:D="yes". If that list has duplicate entries, I would like only the first entry to show up, but for the next entries I would like the cell to be blank. (this is important for the next step) For Column C: I want to have the corresponding dates go with the name entry. For Column D: I want to have the notes go with the corresponding date entry. (I believe I can manipulate Column C's code to do Column D myself).

I am also going to upload a data sheet, and an expected results sheet.

unique list.xlsx

View 1 Replies View Related

Excel 2010 :: Can't Delete Filtered / Visible List Rows From List Object

May 19, 2014

I have pulled a SharePoint list into my workbook. The list object (table) is still linked to the SharePoint list, as I'd like to synchronize it later on. I have filtered it with an autofilter. I'd like to delete all of the visible rows. I have tried a billion things to no avail. I have been searching Google for hours now. None of the examples work.

View 5 Replies View Related

Data Validation List Automatically Show The First Item In The List Rather Than Showing Blank

Jun 20, 2008

I can't seem to find a way to make a data validation list automatically show the first item in the list rather than showing blank.

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved