Hi! I am currently running a VB script to record statistics, but I need to run the excel document on 5 computers at the same time so I am trying to using two files. One excel file with a VB script to record the data and a second excel file to store the data. The problem is that I can’t get it to work. This is the script that I’m using with a User form. Any one got an idea how to make this script record the data to an “external” file? Like opening, add, save and close. And if the file is in use display an error of some sort. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
ffice
ffice" /><o
></o
>
Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")
'find first empty row in database
lRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
lPart = Me.cboPart.ListIndex
'check for a part number
If Trim(Me.cboPart.Value) = "" Or Trim(Me.cboLocation.Value) = "" Or Trim(Me.txtDate.Value) = "" Or Trim(Me.txtQty.Value) = "" Then
Me.cboPart.SetFocus
MsgBox "Et eller flere felt er ikke riktig utfylt"
Exit Sub
End If
'copy the data to the database
With ws
.Cells(lRow, 1).Value = Me.cboPart.Value
.Cells(lRow, 2).Value = Me.cboLocation.Value
.Cells(lRow, 3).Value = Me.txtDate.Value
.Cells(lRow, 4).Value = Me.txtQty.Value
.Cells(lRow, 5).Value = Format(Me.txtDate.Value, "MMMM/yy")
.Cells(lRow, 6).Value = Format(Me.txtDate.Value, "WW") & " - " & Format(Me.txtDate.Value, "YYYY")
End With
'clear the data
Me.cboPart.Value = ""
Me.cboLocation.Value = ""
Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtQty.Value = 1
Me.cboPart.SetFocus
Me.cboLocation.Clear
End Sub
Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")
'find first empty row in database
lRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
lPart = Me.cboPart.ListIndex
'check for a part number
If Trim(Me.cboPart.Value) = "" Or Trim(Me.cboLocation.Value) = "" Or Trim(Me.txtDate.Value) = "" Or Trim(Me.txtQty.Value) = "" Then
Me.cboPart.SetFocus
MsgBox "Et eller flere felt er ikke riktig utfylt"
Exit Sub
End If
'copy the data to the database
With ws
.Cells(lRow, 1).Value = Me.cboPart.Value
.Cells(lRow, 2).Value = Me.cboLocation.Value
.Cells(lRow, 3).Value = Me.txtDate.Value
.Cells(lRow, 4).Value = Me.txtQty.Value
.Cells(lRow, 5).Value = Format(Me.txtDate.Value, "MMMM/yy")
.Cells(lRow, 6).Value = Format(Me.txtDate.Value, "WW") & " - " & Format(Me.txtDate.Value, "YYYY")
End With
'clear the data
Me.cboPart.Value = ""
Me.cboLocation.Value = ""
Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtQty.Value = 1
Me.cboPart.SetFocus
Me.cboLocation.Clear
End Sub