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.
open an excel file and insert a row with data on it then close it back
-
kaelo2014 -
August 21, 2017 at 10:02 AM -
Thread is marked as Resolved.
-
-
-
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 ...?
-
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?Code
Display MoreSub Macro1() ' ' Macro1 Macro ' ' Rows("2:2").Select Selection.Copy Application.Workbooks.Open "\\ZXG-FXVR-01\Compliance_NCC$\zXML_Validation\PEP_DOCUMENTATION\DataBase.xlsb", UpdateLinks:=0 Windows("DataBase.xlsb").Activate Rows("2:2").Select Selection.Insert Shift:=xlDown Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False ActiveWorkbook.Save Workbooks("DataBase.xlsb").Close SaveChanges:=True Windows("testing.xlsb").Activate End Sub
-
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).
Code
Display MoreOption Explicit 'Provides access to the file path provided Sub A_ExternalWorkbookAccess(ByRef strFullPath As String, _ Optional ByRef blnWriteAccessRequired As Boolean = False, _ Optional ByRef blnSuppressAlreadyOpenMsg As Boolean = False, _ Optional ByRef blnSuppressEvents As Boolean = False, _ Optional ByRef blnUpdateLinks As Boolean = False, _ Optional ByRef blnReadOnly As Boolean = True, _ Optional ByRef strPassword As String = vbNullString, _ Optional ByRef strWriteResPassword As String = vbNullString, _ Optional ByRef varTargetWorkbook As Variant) Dim strFilePathCheckMsg As String Dim wbTargetWorkbook As Workbook Dim blnWorkbookOpened As Boolean Dim blnCurrentEvents As Boolean Dim blnCurrentUpdating As Boolean 'Gets current EnableEvents/ScreenUpdating values to reapply before handing control back to calling routines blnCurrentEvents = Application.EnableEvents blnCurrentUpdating = Application.ScreenUpdating 'Turns both off before process starts Application.EnableEvents = False Application.ScreenUpdating = False 'Checks the validity of the File Path String strFilePathCheckMsg = B_ValidFilePathCheck(strFullPath) If strFilePathCheckMsg <> "Path Exists" Then Call A_FilePathError(strFullPath, strFilePathCheckMsg, blnCurrentEvents, blnCurrentUpdating) 'Check if the workbook is already open If B_CheckForOpenWorkbook(strFullPath, wbTargetWorkbook, blnCurrentEvents, blnCurrentUpdating) = True Then 'Checks request against opened file permissions Call A_WriteAccessChecks(blnWriteAccessRequired, wbTargetWorkbook, strFullPath, blnCurrentEvents, blnCurrentUpdating) 'Checks if the "Sheet Open" message has been suppressed If blnSuppressAlreadyOpenMsg = False Then MsgBox "The Workbook is already open in this application", vbCritical, "Workbook Open Error" End If Else 'Sends the request to open the file Call A_OpenFile(wbTargetWorkbook, blnSuppressEvents, blnUpdateLinks, blnReadOnly, strPassword, strWriteResPassword, strFullPath) 'Checks request against opened file permissions Call A_WriteAccessChecks(blnWriteAccessRequired, wbTargetWorkbook, strFullPath, blnCurrentEvents, blnCurrentUpdating) End If If IsMissing(varTargetWorkbook) = False Then Set varTargetWorkbook = wbTargetWorkbook 'Returns ScreenUpdating and EnableEvents back to their starting value 'before returning control to the calling routine Application.ScreenUpdating = blnCurrentUpdating Application.EnableEvents = blnCurrentEvents Set wbTargetWorkbook = Nothing End Sub 'File Path error handler 'Called when there is an issue with the file path, the appropriate error message is supplied with the call Private Sub A_FilePathError(ByRef strFullPath As String, _ ByRef strFilePathCheckMsg As String, _ ByRef blnCurrentEvents As Boolean, ByRef blnCurrentUpdating As Boolean) Dim strMsgBoxContent As String 'Controls the dynamic section of the error message If strFilePathCheckMsg = "No file path has been supplied" Then strMsgBoxContent = "The following issue has been detected:" _ & vbNewLine & vbNewLine & _ " - " & strFilePathCheckMsg _ Else strMsgBoxContent = "You have supplied the following file path:" _ & vbNewLine & vbNewLine & _ strFullPath _ & vbNewLine & vbNewLine & _ "The following issue was detected with this file path:" _ & vbNewLine & vbNewLine & _ " - " & strFilePathCheckMsg End If 'Adds the generic section of the error message strMsgBoxContent = "An error has occurred during processing within the Sheet Access Add-In:" _ & vbNewLine & vbNewLine & _ strMsgBoxContent _ & vbNewLine & vbNewLine & _ "Please address this issue and try again." _ & vbNewLine & vbNewLine & _ "This process will now terminate" MsgBox strMsgBoxContent, vbCritical, "File Path Exception" Call A_TerminateProcess(blnCurrentEvents, blnCurrentUpdating) End Sub 'If the calling routine has requested Write Permission 'but the opened sheet is in Read Only Mode then the process will be sent here Private Sub A_WritePermissionError(ByRef wbTargetWorkbook As Workbook, _ ByRef strFullPath As String, _ ByRef blnCurrentEvents As Boolean, ByRef blnCurrentUpdating As Boolean) wbTargetWorkbook.Close MsgBox "An error has occurred during processing within the Sheet Access Add-In:" _ & vbNewLine & vbNewLine & _ "You have requested Write Permission from:" _ & vbNewLine & vbNewLine & _ strFullPath _ & vbNewLine & vbNewLine & _ "Only Read Permission is currently available. Please check that nobody else" & _ "is currently locked to the file and that the file properties are not set " & _ "to read only." _ & vbNewLine & vbNewLine & _ "This process will now terminate.", _ vbCritical, "Unhandled Exception Error" Call A_TerminateProcess(blnCurrentEvents, blnCurrentUpdating) End Sub 'Generic error handler 'If there is no valid reason for a function to fail it will be passed here 'to confirm the function that failed for further investigation Private Sub A_UnhandledError(ByRef strErrorLocation As String, _ ByRef blnCurrentEvents As Boolean, ByRef blnCurrentUpdating As Boolean) MsgBox "An SheetAccess_AddIn Error has occured while processing the following function:" _ & vbNewLine & vbNewLine & _ strErrorLocation _ & vbNewLine & vbNewLine & _ "Please contact a member of the VBA Working Group to investigate further." _ & vbNewLine & vbNewLine & _ "Ensure you supply full details of the inputs and process that has failed." _ & vbNewLine & vbNewLine & _ "This process will now terminate.", _ vbCritical, "Unhandled Exception Error" Call A_TerminateProcess(blnCurrentEvents, blnCurrentUpdating) End Sub 'Opens the supplied file path, disables/enables events based on instructions 'And sets the Target Workbook object Private Sub A_OpenFile(ByRef wbTargetWorkbook As Workbook, _ ByRef blnSuppressEvents As Boolean, _ ByRef blnUpdateLinks As Boolean, _ ByRef blnReadOnly As Boolean, _ ByRef strPassword As String, _ ByRef strWriteResPassword As String, _ ByRef strFilePath As String) If blnSuppressEvents = True And Application.EnableEvents = True Then Application.EnableEvents = False If blnSuppressEvents = False And Application.EnableEvents = False Then Application.EnableEvents = True Workbooks.Open Filename:=strFilePath, _ ReadOnly:=blnReadOnly, _ UpdateLinks:=blnUpdateLinks, _ Password:=strPassword, _ WriteResPassword:=strWriteResPassword, _ IgnoreReadOnlyRecommended:=True Set wbTargetWorkbook = ActiveWorkbook End Sub 'Controls the Write Access Permission Checks 'Independent from the main control due to the call happening from two places Private Sub A_WriteAccessChecks(ByRef blnWriteAccessRequired As Boolean, _ ByRef wbTargetWorkbook As Workbook, _ ByRef strFullPath As String, _ ByRef blnCurrentEvents As Boolean, ByRef blnCurrentUpdating As Boolean) 'If write access is required it will check for this If blnWriteAccessRequired = True Then If B_WorkbookReadOnlyCheck(wbTargetWorkbook) = True Then Call A_WritePermissionError(wbTargetWorkbook, strFullPath, blnCurrentEvents, blnCurrentUpdating) End If End Sub 'Returns ScreenUpdating and EnableEvents back to their starting value before terminating the process before completion Private Sub A_TerminateProcess(ByRef blnCurrentEvents As Boolean, ByRef blnCurrentUpdating As Boolean) Application.ScreenUpdating = blnCurrentUpdating Application.EnableEvents = blnCurrentEvents End End Sub 'Checks supplied file path is valid and returns appropriate message Function B_ValidFilePathCheck(ByRef strFullFilePath As String) As String If Len(strFullFilePath) = 0 Then B_ValidFilePathCheck = "No file path has been supplied" Exit Function End If If Not Dir(strFullFilePath, vbDirectory) = vbNullString Then B_ValidFilePathCheck = "Path Exists" Exit Function Else B_ValidFilePathCheck = "File path supplied does not exist (check spelling)" Exit Function End If End Function 'Checks if the workbook is already open in the application window Private Function B_CheckForOpenWorkbook(ByRef strFilePath As String, _ ByRef wbTargetWorkbook As Workbook, _ ByRef blnCurrentEvents As Boolean, ByRef blnCurrentUpdating As Boolean) As Boolean On Error GoTo ErrHandler Dim wbTemp As Workbook Dim blnWorkbookOpen As Boolean: blnWorkbookOpen = False 'Checks all open Workbooks for a matching File Path For Each wbTemp In Application.Workbooks If wbTemp.FullName = strFilePath Then blnWorkbookOpen = True Set wbTargetWorkbook = wbTemp GoTo ExitHandler End If Next wbTemp ExitHandler: 'Passes the Temp Result back to the Function B_CheckForOpenWorkbook = blnWorkbookOpen Exit Function ErrHandler: 'No obvious reason for valid failures 'Any errors will be sent to the UnhandledError Sub and the 'Process Terminated Call A_UnhandledError("Z_CheckForOpenWorkbook", blnCurrentEvents, blnCurrentUpdating) End Function 'Passes back whether the workbook is read only Private Function B_WorkbookReadOnlyCheck(ByRef wbTargetWorkbook As Workbook) B_WorkbookReadOnlyCheck = wbTargetWorkbook.ReadOnly End Function
So instead of the line:
CodeApplication.Workbooks.Open "\\ZXG-FXVR-01\Compliance_NCC$\zXML_Validation\PEP_DOCUMENTATION\DataBase.xlsb", UpdateLinks:=0
In your code, you would replace it with:
Codecall 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 programmingi 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)Code
Display More'Checks supplied file path is valid and returns appropriate message Function B_ValidFilePathCheck(ByRef strFullFilePath As String) As String If Len(strFullFilePath) = 0 Then B_ValidFilePathCheck = "No file path has been supplied" Exit Function End If [U][B] If Not Dir(strFullFilePath, vbDirectory) = vbNullString Then[/B][/U] B_ValidFilePathCheck = "Path Exists" Exit Function Else B_ValidFilePathCheck = "File path supplied does not exist (check spelling)" Exit Function End If End Function 'Checks if the workbook is already open in the application window Private Function B_CheckForOpenWorkbook(ByRef strFilePath As String, _ ByRef wbTargetWorkbook As Workbook, _ ByRef blnCurrentEvents As Boolean, ByRef blnCurrentUpdating As Boolean) As Boolean On Error GoTo ErrHandler Dim wbTemp As Workbook [U][B] Dim blnWorkbookOpen As Boolean blnWorkbookOpen = False[/B][/U] 'Checks all open Workbooks for a matching File Path For Each wbTemp In Application.Workbooks If wbTemp.FullName = strFilePath Then blnWorkbookOpen = True Set wbTargetWorkbook = wbTemp GoTo ExitHandler End If Next wbTemp ExitHandler: 'Passes the Temp Result back to the Function B_CheckForOpenWorkbook = blnWorkbookOpen Exit Function ErrHandler: 'No obvious reason for valid failures 'Any errors will be sent to the UnhandledError Sub and the 'Process Terminated Call A_UnhandledError("Z_CheckForOpenWorkbook", blnCurrentEvents, blnCurrentUpdating) End Function
-
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!