Updating Worksheet Through User Form

[Guest]

Hi there!
could any one help me out from a problem in Excel-VBA. I want to update the
row of a worksheet through user form. I did it successfully, but in the same
workbook. Now the real problem is this I want to update the others workbook's
worksheet. Here what I already did .

Private Sub cmdAddStudent_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Students")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

'check for a Student Name
If Trim(Me.txtStudentName.Value) = "" Then
Me.txtStudentName.SetFocus
MsgBox "Please enter a Student Name"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtStudentName.Value
ws.Cells(iRow, 2).Value = Me.txtClassId.Value
ws.Cells(iRow, 3).Value = Me.txtStudentID.Value
ws.Cells(iRow, 4).Value = Me.txtRegNo.Value

'clear the data
Me.txtStudentName.Value = ""
Me.txtClassId.Value = ""
Me.txtStudentID.Value = ""
Me.txtRegNo.Value = ""
Me.txtStudentName.SetFocus
End Sub

The code is working fine but i want to update the same record in another
worksheet which is present in another workbook on network.

The path for that file is

\\sharedrive\ShareFile\Public\StuData.xls

and Worksheet's name is

Students


please guys help me out.

Saddat Sarfraz
Saadi

 

[Lonnie M.]

HI, try adding this after you copy to the 1st database:

myFileNameDir = "\\sharedrive\ShareFile\Public\StuData.xls"
Workbooks.Open Filename:=myFileNameDir, UpdateLinks:=0
Set ws = ActiveSheet.Name

'copy the data to the 2nd database
ws.Cells(iRow, 1).Value = Me.txtStudentName.Value
ws.Cells(iRow, 2).Value = Me.txtClassId.Value
ws.Cells(iRow, 3).Value = Me.txtStudentID.Value
ws.Cells(iRow, 4).Value = Me.txtRegNo.Value
ActiveWorkbook.Save
ActiveWorkbook.Close


HTH--Lonnie M.

 

[Lonnie M.]

Correction: Not ActiveSheet.Name...

Workbooks.Open Filename:=myFileNameDir, UpdateLinks:=0
Set ws = ActiveSheet

 

[Guest]

Thanks Lonnie M, it is wokrking with some little changes. thanks for your
kind help.

Here is the complete code for others, that may be help them in their work.
this code will update the current Workbook's sheet "Students" and then update
the "Students" sheet in the workbook located on share drive.



Private Sub cmdAddQuote_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Students")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

'check for a Student Name
If Trim(Me.txtStudentName.Value) = "" Then
Me.txtCompanyName.SetFocus
MsgBox "Please enter a Student Name"
Exit Sub
End If

'copy the data to the 1st database
ws.Cells(iRow, 1).Value = Me.txtStudentName.Value
ws.Cells(iRow, 2).Value = Me.txtClassId.Value
ws.Cells(iRow, 3).Value = Me.txtStudentID.Value
ws.Cells(iRow, 4).Value = Me.txtRegNo.Value


Dim myFileNameDir As String
Dim ws1 As Worksheet
Dim iRow1 As Long

myFileNameDir = "\\sharedrive\ShareFile\Public\StuData.xls"
Workbooks.Open Filename:=myFileNameDir, UpdateLinks:=0
Set ws1 = Worksheets("Students")

'find first empty row in database 2
iRow1 = ws1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

'copy the data to the 2nd database
ws1.Cells(iRow1, 1).Value = Me.txtStudentName.Value
ws1.Cells(iRow1, 2).Value = Me.txtClassId.Value
ws1.Cells(iRow1, 3).Value = Me.txtStudentID.Value
ws1.Cells(iRow1, 4).Value = Me.txtRegNo.Value

ActiveWorkbook.Save
ActiveWorkbook.Close


'clear the data
Me.txtStudentName.Value = ""
Me.txtClassId.Value = ""
Me.txtStudentID.Value = ""
Me.txtRegNo.Value = ""
Me.txtStudentName.SetFocus
End Sub