open an excel file and insert a row with data on it then close it back

  • i have an excell workbook where i fill a row with data but anytime i click on a button it would copy the entire row 2 then goes to open a file in the network and inserts the copied row on sheet1 row2 of the new file on the network.
    i would be having many of this files on different computers inserting on the same file in the network.

  • Re: open an excel file and insert a row with data on it then close it back


    Hello to you,


    Why don't you post your current VBA code ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: open an excel file and insert a row with data on it then close it back


    Hi,
    below is my code.
    its working but when more than one person is trying to write (paste to the database file). it says the file is locked for editing by another user.
    is there a way i can overcome this problem?


  • Re: open an excel file and insert a row with data on it then close it back


    HELLO


    is there a way i can auto notify when another user is pasting that is when the Dbase file is locked for editing such that it can allow data to be pasted.


    counting on you

  • Re: open an excel file and insert a row with data on it then close it back


    This may help you (although maybe a lengthy solution). It's all the code for an add-in I had written a while back to handle opening other workbooks where different access may be required (saved me rewriting the same thing every time I needed a bespoke solution).



    So instead of the line:


    Code
    Application.Workbooks.Open "\\ZXG-FXVR-01\Compliance_NCC$\zXML_Validation\PEP_DOCUMENTATION\DataBase.xlsb", UpdateLinks:=0


    In your code, you would replace it with:


    Code
    call A_ExternalWorkbookAccess(strFullPath:="\\ZXG-FXVR-01\Compliance_NCC$\zXML_Validation\PEP_DOCUMENTATION\DataBase.xlsb", _
                                                  blnWriteAccessRequired:= TRUE, blnReadOnly:=FALSE)


    Alternatively you can make the workbook shared, not sure what issues that may cause with users overwriting each other's data though.

  • Re: open an excel file and insert a row with data on it then close it back


    wow
    i think this coding is a bit too much me.
    am still young in programming


    i will take time to studdy it.
    is all this codes for just openning the dbase file?

  • Re: open an excel file and insert a row with data on it then close it back


    Provided that when you say dBase File you actually mean the Excel Binary File (as opposed to something like an actual Access Database) then yes.


    It's the full code that I created sometime back for standardising the opening of workbooks. So on my system it sits in a separate excel add-in (.xlam) that can be referenced from any code that I write so I don't have to bother coding the opening of sheets every time.


    So the only mandatory variable that needs passed into it is the full file path, all the others are optional around the type of access you need.


    Depending on what's passed into it, it will attempt to open the file, check that access it has against what you requested and either fire an error message confirming that it can't meet your requested access requirements just now (you need write access and someone is locked to it limiting you to read access for the moment being the main issue), or the file is opened as requested.


    The last variable/object allows you to pass an actual workbook object as well, if this is supplied then it will set that object for you before passing it back to your calling code, at which point you can refer to the newly opened workbook as that object, rather than having to mess about with the full file path.


    To be honest I'd probably tidy things up a bit if I did it again now, but it should still be fit for purpose.


    The idea for you is not to worry too much about it, but just drop it in a new module in your project, call it from your code and give it the appropriate variables and objects. It should hopefully just work without you having to worry too much about what is going on, although I accept you don't know me and probably want to be sure I'm not stealing your bank details or some other nefarious scheme.

  • Re: open an excel file and insert a row with data on it then close it back


    thanks though.
    Am seing a lot of logic and am slowly trying to figure out the codes.
    how will these ensure that when multiple users are kept in read only, that data inserted would eventually be saved.

  • Re: open an excel file and insert a row with data on it then close it back


    hi
    i have error on these lines boldened and underlined.
    the if not directory is giving me bad file name or number error (runtime error 52)


  • Re: open an excel file and insert a row with data on it then close it back


    I'll try and take a look at the issues when I have a moment.


    As to how it can help with the writing of the data, basically you would use the add-in to request write access to the file, if it can't give you write access then it would return an error message confirming that write access isn't available at that time (you may want alter the message to confirm to try again in a moment).


    My assumption is that it should only be getting opened by this process so people shouldn't be sitting in it for long.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!