CODEWITHSUNDEEP
vbaexcel
Sergio Rojas
Sergio Rojas

Reputation: 11

VBA- Command Button Saved TextBox value into a different Workbook

I need some help creating a code that will allow me to save data input into a TextBox to a different Workbook called FoundData.xlsm - sheet FoundData

Workbook location = FileShare-MFG (N:)/Manufacturing/PhysicalInventory/Tools

So far I was able to use a CommandButton to save into a worksheet on the same file.

I would really appreciate any input and help.

Attached bellow it's the current VBA code.

Thanks in advance, 

Private Sub cmdSave_Click() 
    Dim lRow As Long 
    Dim lPart As Long 
    Dim wb As Workbook 
    Set wb = Worksheets("FoundItems") 

    'find first empty row in database 
    lRow = wb.Cells.Find(What:="*", SearchOrder:=xlRows, _ 
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1 

    With wb 
        ' .Unprotect Password:="password" 
        .Cells(lRow, 1).Value = Me.txtQuarter.Value 
        .Cells(lRow, 2).Value = Me.txtDate.Value 
        .Cells(lRow, 3).Value = Me.txtTSN.Value 
        .Cells(lRow, 4).Value = Me.txtPN.Value 
        .Cells(lRow, 5).Value = Me.txtQty.Value 
        .Cells(lRow, 6).Value = Me.txtLoc.Value 
        .Cells(lRow, 7).Value = Me.txtName.Value 
        .Cells(lRow, 8).Value = Me.txtComments.Value 
        ' .Protect Password:="password" 
    End With 

    'clear the data 
    Me.txtDate.Value = "" 
    Me.txtTSN.Value = "" 
    Me.txtPN.Value = "" 
    Me.txtQty.Value = "" 
    Me.txtLoc.Value = "" 
    Me.txtComments.Value = "" 
End Sub

Upvotes: 1

Views: 1190

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57743

If the workbook is already opened you can just reference it 

Dim Ws As Worksheet
Set Ws = Workbooks("FoundData").Worksheets("FoundItems")

Note that you must use a worksheet object not a workbook object as you did. So Dim ws As Worksheet here.

If it is closed you can open it with the Workbooks.Open Method

Dim Wb As Workbook
Set Wb = Workbooks.Open(FileName:="N:\Manufacturing\PhysicalInventory\Tools\FoundData.xlsm")

and then reference the worksheet with

Dim Ws As Worksheet
Set Ws = Wb.Worksheets("FoundItems")

Upvotes: 1

Related Questions