+ Reply to Thread
Results 1 to 16 of 16

How to stop/restrict manipulation of workbooks

  1. #1
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    How to stop/restrict manipulation of workbooks

    Hi, I receive different excel workbooks from different areas. These workbooks are extracted/exported from other software(Like SAP or other) by different users, the issue is that i don,t know either these are original workbooks or manipulated workbooks which are sent to me for processing/dashboard. Now how can i ensure that these workbooks are not touched/manipulated by the users? For-example: if Operator 1 is sending me an excel file which contains ten customers data, he export it from other software. Before sending, the operator changes 2 or 3 customers data and then send it to me. Now how can i stop this thing from happening? and how can i know that data is changed or not?
    Any suggestions/help would highly be appreciated.
    Thanks
    i have posted this question here too: https://stackoverflow.com/questions/...n-of-workbooks
    Last edited by Zahid0111; 03-31-2020 at 05:31 AM.

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: How to stop/restrict manipulation of workbooks

    If the user is suing a file you give him/her you can protect it in such a manner that only macro code can modify it but not the user unless he/she knows the password to unlock it an make modifications.
    Without any knowledge of how you're handling the Excel files, guessing would be the only option to answer, but I am not going for guessing or assumptions.
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: How to stop/restrict manipulation of workbooks

    @Keebellah..thanks for reply.. Well as i told, Excel file is generated by software, i mean several users are using this software and when data is exported from this software by any user, it is in excel file. This excel file is then sent to me.

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: How to stop/restrict manipulation of workbooks

    There is no way for you to tell what is happening to the spreadsheet before you receive it. It would be nice if Excel had an audit trail that the company/business could lock to the on position but Microsoft does not provide this. You could do spot checks (if you have access to the same data and software) or trust your business associates. From a regulatory perspective, there is a serious data integrity issue here.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: How to stop/restrict manipulation of workbooks

    Excel can tell you who last saved the file but not the tracking, that's the reason SAP and Oracle are commonly accepting as auditing tool.
    Since the Excel file is generated by a third-party it's outside Excel's domain until the user opens it.
    I don't think you'll be able to do anything about that unless you write the code to create the connection into an existing file which the user send to you

  6. #6
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: How to stop/restrict manipulation of workbooks

    @ChemistB & Keebellah : thanks for your time..may be i should ask them to send me in pdf form but it will be difficult for me to extract data from pdf file to excel sheet..is there any way, i can create a macro which can read from pdf and write it to m excel file? i need your kind expertise please..

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: How to stop/restrict manipulation of workbooks

    To convert pdf to xls* you will probably need proprietary software which you will need to buy. Google "Convert PDF to Excel" to see your options for this type solution.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  8. #8
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: How to stop/restrict manipulation of workbooks

    @alansidman ok thank you but there must be some way out... if excel can write to pdf file, there must be some way through which we can read data from it and paste in excel

  9. #9
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: How to stop/restrict manipulation of workbooks


  10. #10
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: How to stop/restrict manipulation of workbooks

    Getting the data in pdf format does not alleviate your problem. They can easily manipulate the data before they convert it to a pdf format and send it to you. I don't see a reason to switch to pdf unless it is passing through other people's hands before it gets to you.

  11. #11
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: How to stop/restrict manipulation of workbooks

    Unless you can make the data application export it as pdf instead of excel

  12. #12
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: How to stop/restrict manipulation of workbooks

    Good point, Keebellah but how would you know if they exported then converted or exported as pdf?

  13. #13
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: How to stop/restrict manipulation of workbooks

    Yeah this ChemistB is right, it still cannot be trusted...

  14. #14
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: How to stop/restrict manipulation of workbooks

    @ChemistB @Keebellah can you please help me with this:

    https://www.excelforum.com/excel-pro...xcel-file.html

  15. #15
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900
    Quote Originally Posted by ChemistB View Post
    Good point, Keebellah but how would you know if they exported then converted or exported as pdf?
    All files get as attribute the user or system that created the file and also the last time saved and by whom so if this last time stamp differs the file has been manipulated

  16. #16
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: How to stop/restrict manipulation of workbooks

    Hmm good point..Keebellah

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA loop through all open workbooks and stop at first
    By JM0308 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 09-20-2017, 07:30 AM
  2. How do I stop this popup in one of my excel workbooks
    By alpha_lsdjf in forum Excel General
    Replies: 5
    Last Post: 11-02-2016, 05:23 AM
  3. Why my workbooks stop cycling my Macro's ??
    By black7 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-23-2013, 04:11 PM
  4. Stop workbooks from being saved
    By BarryTSL in forum Excel General
    Replies: 8
    Last Post: 02-04-2011, 01:50 PM
  5. stop opening several workbooks
    By bakert in forum Excel General
    Replies: 18
    Last Post: 01-30-2011, 04:47 AM
  6. Excel 2007 : How to stop multiple workbooks from opening
    By novice2430 in forum Excel General
    Replies: 7
    Last Post: 01-12-2010, 10:34 AM
  7. [SOLVED] For better Performance in VBA for Excel - Strings manipulation OR Objects manipulation
    By vmegha in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-18-2005, 08:20 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1