Writing data to separate excel file.

[pinex81]

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-comfficeffice" /><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

 

[pinex81]

Hi! Here is the script I ended up with if anyone is interested. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
Application.ScreenUpdating = True
Dim SourceWB As Workbook
Dim ws As Worksheet
On Error Resume Next
GetObject ("c:\test02.xls")
If Err.Number = 0 Then
Set ws = Workbooks("test02.xls").Worksheets("Ark1")
GetObject("c:\test02.xls").Application.Visible = True
GetObject("c:\test02.xls").Windows(1).Visible = True
'Workbooks("test02.xls")
'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
Else
Me.cboPart.SetFocus
MsgBox "Noen andre bruker filen prøv igjen"
Exit Sub
End If
'clear the data
GetObject("c:\test02.xls").Close SaveChanges:=True
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