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 RepliesWe 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] .....
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.
I was able to create a recordset of data and then save the recordset as an XML document. This XML document is aggregated data for a chart in Excel.
I need to hit the XML with ADO into a recordset from my select statement...
As you can see below, I think I found the Driver I need to use??? I am not sure that this is correct for Excel VBA ADO???
sSQL = "Select * From C:ADO.XML"
When I open the recordset.open sSQL, cnDB the error I get is this:
"The filename, directory name, or volumne label syntax is incorrect"
Set GetXMLDB = New ADODB.Connection
With GetXMLDB
.Open "Provider=MSDAOSP; Data Source=MSXML2.DSOControl.2.6;"
.CursorLocation = adUseClient
End With
Is it possible to extract data via a webquery from an online PDF document? Like this one: http://www.paalp.com/_filelib/FileCa...r2007Recap.pdf
View 2 Replies View RelatedI currently have a code that searches a folder, opens a spreadsheet, and then copies over the "Status" tab to the Master document.
New spreadsheets will be created a couple times a week, so I would like the code to be able to search this folder for the most recently modified document and then open THAT document.
So, if my folder has..
Report 10May11
Report 05May11
Report 17May11
Then, I would want a code to search in this folder for the most recently updated document (in this case "Report 17May11.xlsx) and then go through the step I have above where it copies over the various tab.
Basically, this code would I guess replace the example code I have below.
Sub WorkbookOpen()
....
End Sub
Anyway to use VBA to print an Excel sheet with the Microsoft Document Image Writer to a specified folder automatically? I understand and use automatic printing all the time, but I don't know how to use VBA to specify the filename and folder once it prints with the Document Image Writer.
Is it possible to do?
Is there a way to write somethign that will go to a particular folder on your computer and bring back the Names of all files in that folder?
I need to build a sheet that lists certain information from each file, which is already included in the file name - trying to avoid opening hundreds of files just to get data from one cell....for example:
All files are in the following folder: ....
I have an excel database where I register cases. I have in it a button that creates a folder with and ID nr that is in column A (I create new ID nr in the next row, when I press the button it will create a folder with that ID nr and inserts a blank word document in it). We have a template that we copy to the folder (depending what type of case). The idea would be that once the template is filled in and ready to print, It would take the values from the ID nr and a reference number a few cells to the right. Is it possible to tell excel to open the word document in the folder and create a PDF version with the ID nr and reference number. (there are only 2 templates, so the macro would have to look for one of the two in the folder) The names of the templates are: "Standard" and "Other". I guess the best way to start maybe this would be that I select the cell with the ID nr and then press a macro button to have this done. One thing that needs to be done, is to put a copy in the same folder and another in a second folder called "Binder" in my documents folder.
View 1 Replies View RelatedHi there, I have searched for this problem and whilst I can see several "round-about" ways to achieve what I want, none quite do it in a way I would like.
Essentially, I have a dropdown box, with a list of water types. What is selected from the dropdown determines the data from a lookup table that gets utilised in subsequent calculations.
The lookup table is protected and cannot be edited, however what I would like to do is for the last entry on the dropdown box, which could be called "special water" would then prompt for a password. If the correct password is entered than the last row of data corresponding to "special water" in the lookup, becomes unlocked and can be edited. If the password is not forthcoming the user cannot select special water and consequently cannot edit the corresponding row in the lookup.
I can think of a few long-winded ways to achieve the basic result, but nothing seems elegant....or fool proof
I had thought about a simple check box against the appropriate row in the lookup called "unlock" which would require a password to use. Once the password is entered, then it can remain open until the check box is clicked again whereby a password is required to "lock" it again
I should add that I use 2003.
I am processing a fair few Invoices, which are being sent to me via e-mail as excel documents, very often they contain mistakes, a decent amount of mistakes. Usually the prices are wrong.
I keep track of every single entry on the invoice on my own document - Tracker, which I consider to be the superior/more correct document to the Invoice presented to me by my contractor.
Both of the documents have a reference number, which is a specific docket number, and horizontally, in the invoice, there is going to be a price for this docket. In my document, there is going to be a separate column for the total price.
Is it possible (I guess with VBA) to check for mistakes in the Invoice, but use the Tracker as a reference for this check.
Tracker has
columns
A - Name
B - Department
C - Date
D - Docket No.
C - Total price for the docket (calculation of E to Z)
E to Z - all smaller entries
Invoice has
columns
A - Date
B - Docket No.
C to E price for that docket, but it is spread, because departments are separated out, so each VAT account can be charged accordingly. I guess it is possible to do a separate column for the price, if it is easier to do a script that way.
Basically, I need to check if in the Invoice document, the price (C to E) for Docket No. (B) is the same as the price (C) for the Docket No. (D) in the Tracker.
I would like the wrong entries to be highlighted on the Invoice Document, so I can see straight away, that this needs attention.
Not always the price is wrong, sometimes the Docket No. is spelled incorrectly (Dyslexic contractor), hence the highlighting.
I need a script that will turn a excel doc into a txt doc. Thats the easy part. The hard part (at least I think it is), is I need it to be in a certain format and I'll do my best to explain that fomat below.
View 9 Replies View RelatedI have set a password under the permissions tab but all it does is protect the structure and not the content. I want all users to be able to view the file, but have to have the password to edit any of the sheets or data on the sheets. I can still protect the individual sheets, but that doesnt quite do what I want.
View 1 Replies View RelatedI have an the excel book with sheets that are password protected so that the users cannot delete rows or columns.
In each sheet I gave permissions for certain ranges that need to be filled out only to specific users that need to fill out those cells.
The users are located and managed in the Active Directory.
The point is that each user can only update his/her sheet within the Excel book.
It was working for a day or two and then all of a sudden without any changes all the permissions were gone and every user was able to edit every sheet.
I am putting together a corporate document which requires many different peoples access. It also requires these people to update/comment within specific columns to them.
I am really struggling with editing the columns to ensure only these specific people can access them.
Essentially I want to the document to be accessed by everyone, but column A can only be edited by Person A, column B can only be edited by Person B etc.
I've used 'Allow Users to Edit Ranges' and added a new range with a specified person - but this has not cured the problem, other people can still edit the column I'm trying to prevent them to.
Is it a case of just 'allowing' the specific person, or do I also need to 'deny' absolutely every other person to ensure that it works?
I have a file I created which works fine for me, on my machine at least but there are two issues I'm trying to sort out.
The first issue is I need to share the file with others who may not have the same access rights. I have something loading in the Workbook_Open event which I want to bypass for users who do not have access to a specific folder. So I was trying to check their access using Dir(path) = "" at the top level folder to see if any files are present (which there are). My assumption was that if it finds files it's safe to assume they have permission and it's ok to continue, otherwise they don't in which case I want abort the rest of the code. This seems to work fine for me but I tried testing with someone I know does not have access and it gave her a compile error.
Part of the code in macro utilizes FileSystemObject which seems to be the line where the code bombs on my coworkers machine. The FSO seems related to appears related to the Microsoft Scripting Runtime reference, but it's not enabled on my machine or any of the others. I vaguely remember having to do something on my machine awhile back for the life of me don't remember what it was.
The second issue is an MS Forms error: "Could not load some objects because they are not available on this machine." appears when opening the file on three of the machines I tested but it does not appear on my machine. I've tried looking at the references and could not find any differences between the machines. I pored through various postings online and it appears it might be worth re-registering the DLL/OCX files but we need admin rights to do that so I will need to open a request with our support team.
I need to get the data from an MDB file. No problems normally but in this case the MDB is protected with a workgroup file (MDW) from the old user level permissions that was available in Access.
Is it it possible to get data from the Data ribbon from a protected file or do I need to resort to vba?
I have been tasked with trying to find out the permissions on a set of folders on a company network. I know write vba code to manipulate a excel spreadsheet without any problems, but it is when i'm using objects that it starts to go beyond my capability. whilst searching i found this code:
Code:
Sub test1()
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const FullAccessMask = 2032127, ModifyAccessMask = 1245631, WriteAccessMask = 118009
Const ROAccessMask = 1179817
[code].....
It does a perfect job but puts the data into an html file. i managed to get it to input the data into the spreadsheet as well but i would like to clean this code up so that it is not producing another file (namely the text file or the html file) and just put the results into the excel file I have entered the module into.
I did start to remove the references to any file name, but that is where i run into trouble and it wouldn't run any more due to object not found.
I'm on Win 7 using Office 2010
I am having an issue with Excel creating a duplicate file when logged in as a user with restricted permissions on our network. If I modify a file while logged in as another user, excel will save the file but will also create a duplicate file with an eight character random name and no file extension. For example if user "Dave" opens a file called test.xls and saves it, in that same folder on the server a file named 296E9E20 will be created. Users are using Windows XP SP3 boxes and the server is running Win 2003. I am not sure why this only happens with Excel.
View 3 Replies View RelatedI currently have a password secured excel file on my departs Q drive, which everyone can access unfortunately only 1 person can edit at a time. I tried to turn it into a workbook so we could all edit it at the same time but now some of the other users can't access the file at our other buildings throughout the county. This is for excel 2010. And I did select the share workbook, and allow multiple users to edit box. everything else is unchanged.
View 3 Replies View RelatedFrom 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 View RelatedI 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.....
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
I would like to find out the excel VBA code to copy files to a sharepoint directory ?
View 5 Replies View RelatedI need to upload the excel file (Book1.xlsx) which is placed in my desktop to the sharepoint 2010.
View 1 Replies View RelatedI 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?
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.
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.
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..
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].....