Send data to next available row in a closed protected workbook using a Loop

[jrtraylor]

I have 2 workbooks. One is a Work Order that I have programmed several user forms to populate different sheets. This document is set up using named ranges to make my coding easier for people to understand in my company. The second is a Custom Panel Log (Database in excel) that we use to track what project was assigned this years serial numbers (it is password protected on opening).

What I need ot do is find a way to use a Button on the Work Order Activation Sheet to
1. Open the Custom Panel Log using the password
2. Find the next empty row starting in column B (Column A has the predefined serial numbers)
3. Start a Loop using the Qty field (Named Range) in the General Info Sheet of the Work Order to fill in the following ranges into the correct columns in the Custom Panel Log
A. Custom Panel Log.xls.Sheets("2012").Cells(nextemptyrow, "D").Value = Work Order.Sheets("General Info").Range("JOB").Value
B. (nextemptyrow, "E").Value = Range("Customer").Value
C. (nextemptyrow, "F").Value = Range("Lot").Value
D. (nextemptyrow, "G").Value = Range("CustomerPO").Value
E. (nextemptyrow, "H").Value = Range("PartNo").Value

If the Value in the Work Order Qty field is 30, I need the process to loop and fill the next 30 empty rows in the custom panel log then save the file and close.

I have the following code that i have tried to setup and find the next empty row bt it is not working. It will run with no errors but will not transfer any data to the custom panel log. I do not know how to make it loop with the value of the qty field or if this code is even correct. I am doing something similar to this where my database has a button and fills my form in a different workbook, i tried tomodify the code for that to get this.

Private Sub AssignPanelNocmnd_Click()
  
Dim xNewApp As New Excel.Application
Dim xNewWB As New Excel.Workbook
Dim strFile As String
Dim RowCount As Long
Dim Ws1 As Worksheet, Ws2 As Worksheet
'***You must change the file path and extension below as applicable***
strFile = "C:\Documents and Settings\JTraylor\Desktop\Forms for DST By Jimmie\Copy of Custom Panel Log Test.xls"
Set xNewWB = xNewApp.Workbooks.Open(strFile)
Set Ws1 = ThisWorkbook.Sheets("General Info")
Set Ws2 = xNewApp.Sheets("2012")
Ws2.Activate
Ws2.Range("B1").Activate
RowCount = Ws2.Range("B1").CurrentRegion.Rows.Count
    With Ws2.Range("B1")
     .Offset(RowCount, 2) = Ws1.Range("JOB").Value
     .Offset(RowCount, 3) = Ws1.Range("Customer").Value
     .Offset(RowCount, 4) = Ws1.Range("Lot").Value
     .Offset(RowCount, 5) = Ws1.Range("CustomerPO").Value
     .Offset(RowCount, 6) = Ws1.Range("PartNo").Value
    End With
xNewWB.Save
xNewWB.Close
Set xNewWB = Nothing
Set xNewApp = Nothing
End Sub

I can send copies of these files to anyone who would like to help me out. If i can get this done for this process i will be able to automate several other process that we spend about 7-15 hours a week doing. (Note i am using Excel 07 but saving my files still as 2000-2003 as many of the users in the company have not been upgraded to newer versions of Office)

 

[jrtraylor]

Ok, i was able to work out this code to use the value of a cell from one worksheet and fill in rows for a worksheet in the same workbook but now need to figure out how to do it in a different workbook while finding the next empty row. Note i have only been doing VBA programming for about a month now.

Sub sampleloop()
    For i = 1 To Sheets("General Info").Range("Qty").Value
       Cells(i, 3).Value = Sheets("General Info").Range("JOB").Value
       Cells(i, 4).Value = Sheets("General Info").Range("Customer").Value
       Cells(i, 5).Value = Sheets("General Info").Range("Lot").Value
       Cells(i, 6).Value = Sheets("General Info").Range("CustomerPO").Value
       Cells(i, 7).Value = Sheets("General Info").Range("PartNo").Value
    Next i
End Sub

 

[jrtraylor]

Still need help with this. I have now modified my code to incluede the loop code that i found along with finding the last row with data to utilize the offset property because that was all i was able to get to work. Now my problem is, the data from the loop will not show up in the the new work book Custom Panel Log but rather on the same sheet that has the command button. I can see where the custom pamel log is opening and selecting range B2 and saving becuase i save the workbook with a different cell selected then run my code. The data is entered into my Activation sheet starting from B2 if i put data in the custom panel log at B2 the data populates in the activation sheet at b3. What am i doing wrong to not send the data to the correct Workbook/Worksheet?

Private Sub AssignPanelNocmnd_Click()
 
Dim xNewApp As New Excel.Application
Dim xNewWB As New Excel.Workbook
Dim strFile As String
Dim Ws1 As Worksheet, Ws2 As Worksheet
'***You must change the file path and extension below as applicable***
strFile = "C:\Documents and Settings\JTraylor\Desktop\Forms for DST By Jimmie\Copy of Custom Panel Log Test.xls"
Set xNewWB = xNewApp.Workbooks.Open(strFile)
Set Ws1 = ThisWorkbook.Sheets("General Info")
Set Ws2 = xNewApp.Sheets("2012")
Ws2.Activate
   Ws2.Range("B2").Activate
      
     nextrow = Ws2.Range("B65536").End(xlUp).Row 'Finds the last cell of data in column B
        Cells(nextrow, 2).Select 'Activates the cell found above
  
    For i = 1 To Ws1.Range("Qty").Value 'Uses the Qty entered into the General information sheet to loop
       ActiveCell.Offset(i, 0).Value = Ws1.Range("JOB").Value
       ActiveCell.Offset(i, 1).Value = Ws1.Range("Customer").Value
       ActiveCell.Offset(i, 2).Value = Ws1.Range("Lot").Value
       ActiveCell.Offset(i, 3).Value = Ws1.Range("CustomerPO").Value
       ActiveCell.Offset(i, 4).Value = Ws1.Range("PartNo").Value
    Next i
xNewWB.Save
xNewWB.Close
Set xNewWB = Nothing
Set xNewApp = Nothing
End Sub

 

[jrtraylor]

I was able to figure this out part of this on my own. The following code will open a protected workbook, find the last row of data and place new data into the next empty row(s) based on a number value in the current workbook. Input boxes have been added to allow for user name and date input.

ANYONE KNOW HOW TO POPULATE A RANGE OF CELLS FROM A COLUMN IN A DIFFERENT WORKBOOK BASED ON 2 CELL VALUES?

Private Sub AssignPanelNocmnd_Click()
 
Dim xNewApp As New Excel.Application
Dim xNewWB As New Excel.Workbook
Dim strFile As String
Dim Ws1 As Worksheet, Ws2 As Worksheet
Dim Username As String
Dim IDate As Date
Username = InputBox("Please Enter Your Name")
IDate = InputBox("Please Enter the Issue Date")
'***You must change the file path and extension below as applicable***
strFile = "C:\Documents and Settings\JTraylor\Desktop\Forms for DST By Jimmie\Copy of Custom Panel Log Test.xls"
Set xNewWB = xNewApp.Workbooks.Open(strFile, Password ="PASSWORD")
Set Ws1 = ThisWorkbook.Sheets("General Info")
Set Ws2 = xNewApp.Sheets("2012")
Ws2.Activate
       
     nextrow = Ws2.Range("D65536").End(xlUp).Row 'Finds the last cell of data in column B
        xNewApp.Cells(nextrow, 2).Select 'Activates the cell found above
  
    For i = 1 To Ws1.Range("Qty").Value 'Uses the Qty entered into the General information sheet to loop
       xNewApp.ActiveCell.Offset(i, 0).Value = Username
       xNewApp.ActiveCell.Offset(i, 1).Value = IDate
       xNewApp.ActiveCell.Offset(i, 2).Value = Ws1.Range("JOB").Value
       xNewApp.ActiveCell.Offset(i, 3).Value = Ws1.Range("Customer").Value
       xNewApp.ActiveCell.Offset(i, 4).Value = Ws1.Range("Lot").Value
       xNewApp.ActiveCell.Offset(i, 5).Value = Ws1.Range("CustomerPO").Value
       xNewApp.ActiveCell.Offset(i, 6).Value = Ws1.Range("PartNo").Value
    Next i
xNewWB.Save
xNewWB.Close
Set xNewWB = Nothing
Set xNewApp = Nothing
End Sub