PDA

View Full Version : Code Problem Saving Data To Another Workbook



Mooseman60
10-29-2010, 08:21 PM
I have the following code to enter data in a userform and save it to a separate workbook called Defined Names Lists.xls to a worksheet in that workbook file called JobNames

I am entering a Job Name & Job No but keep getting the following error for the line of code below Run-time Error 13 Type mismatch


Set wb = Workbooks("Defined Name Lists.xls").Worksheets("JobName").Range("A2").Value = txtJobName.Text





Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Workbook
Set wb = Workbooks("Defined Name Lists.xls").Worksheets("JobName").Range("A2").Value = txtJobName.Text
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a JobName
If Trim(Me.txtJobName.Value) = "" Then
Me.txtJobName.SetFocus
MsgBox "Please enter a Job Name"
Exit Sub

End If
'check for a JobNo
If Trim(Me.txtJobNo.Value) = "" Then
Me.txtJobNo.SetFocus
MsgBox "Please enter a Job No"
Exit Sub

End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtJobName.Value
ws.Cells(iRow, 2).Value = Me.txtJobNo.Value
If Me.txtJobNo.Value <> "" Then
Dim wbOutput As Workbook
Set wbOutput = Workbooks.Add
With wbOutput
.Title = "Job Number "
.Subject = "Job Number created " & Date
.Author = "Script generated"
.SaveAs Filename:=mydir & "\" & Me.txtJobNo.Value
End With
End If

'clear the data
Me.txtJobName.Value = ""
Me.txtJobNo.Value = ""
Me.txtJobNo.SetFocus
Exit Sub

End Sub

:banghead:

Ryan Remole
10-29-2010, 08:50 PM
Set wb = Workbooks("Defined Name Lists.xls")
wb.Activate
Worksheets("JobName").Range("A2").Value = txtJobName.Text

Mooseman60
10-29-2010, 09:02 PM
Set wb = Workbooks("Defined Name Lists.xls")
wb.Activate
Worksheets("JobName").Range("A2").Value = txtJobName.Text

Ryan

That solved that problemn now it coes up with error 91 Object variable or with block variable not set


on this line of code

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtJobName.Value
ws.Cells(iRow, 2).Value = Me.txtJobNo.Value

mdmackillop
10-30-2010, 07:49 AM
I guess you are not using Option Explicit, otherwise the fundemental errors would become apparent

Dim iRow As Long
Dim wb As Workbook
Dim ws As Worksheet
Set wb = Workbooks("Defined Name Lists.xls")
Set ws = wb.Worksheets("JobName")
ws.Range("A2").Value = txtJobName.Text
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Ryan Remole
10-30-2010, 02:29 PM
Ryan

That solved that problemn now it coes up with error 91 Object variable or with block variable not set


on this line of code

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtJobName.Value
ws.Cells(iRow, 2).Value = Me.txtJobNo.Value



I don't think you need to include 'Me' on your controls. 'Value' is optional.

Mooseman60
10-30-2010, 06:35 PM
:banghead: I am still getting variable not set on this line

ReDim Preserve Name(1 To nList)



Option Explicit
Const cListFile As String = "Defined Name Lists.xls" ' the file containing the combobox data
Const cListSheet As String = "JobName" ' the worksheet containing the list
Const cNameColumn As String = "a" ' the column containin gthe list data
Const cJobColumn As String = "b"
Const cHasHeader As Boolean = False ' does the list have a header in row 1
Private Sub UserForm_Initialize()
' open the workbook containing the data to load in combox1 read only
' and hide it
Dim iRow As Long
Dim wb As Workbook
Dim ws As Worksheet
Set wb = Workbooks("Defined Name Lists.xls")
Set ws = wb.Worksheets("JobName")
ws.Range("A2").Value = txtJobName.Text
Set theWB = Workbooks.Open(Filename:="Defined Name Lists.xls", ReadOnly:=True)
theWB.Windows(1).Visible = False


' address the combox list
Dim theSheet As Worksheet
Set theSheet = theWB.Worksheets(cListSheet)
' array size counter
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Dim nList As Long
nList = 0

' loop through all of the list loading the values into the combobox
Dim rw As Range
For Each rw In theSheet.Rows
' skip row 1 if there is a header
If (rw.Row = 1 And cHasHeader) Then
' stop on the first blank cell
ElseIf (rw.Cells(1, 1).Value = "") Then
Exit For
Else
' move the data from the list workbook to the combox
cboJobName.AddItem rw.Cells(1, 1).Value
' retain the job name and job no
nList = nList
ReDim Preserve Name(1 To nList)
ReDim Preserve Job(1 To nList)
Names(nList) = rw.Cells(1, cNameColumn).Value
Jobs(nList) = rw.Cells(1, cJobColumn).Value
End If
Next rw

' close the workbook and release storage
theWB.Close SaveChanges:=False
Set theWB = Nothing
Set theSheet = Nothing


End Sub

mdmackillop
10-31-2010, 03:04 AM
Two thoughts,
Should Name be Names
I don't see nList being incremented so you are dimming (1 to 0)

Mooseman60
10-31-2010, 05:10 AM
:banghead:
It now comes up with error subscript out of range any suggestions i have attached my workbook

This the current code



Private Sub cmdAdd_Click()
Dim iRow As Long
Dim wb As Workbook
Dim ws As Worksheet
Set wb = Workbooks("Defined Name Lists.xls")
Set ws = wb.Worksheets("JobName")
ws.Range("A2").Value = txtJobName.Text
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

'check for a JobName
If Trim(Me.txtJobName.Value) = "" Then
Me.txtJobName.SetFocus
MsgBox "Please enter a Job Name"
Exit Sub

End If
'check for a JobNo
If Trim(Me.txtJobNo.Value) = "" Then
Me.txtJobNo.SetFocus
MsgBox "Please enter a Job No"
Exit Sub

End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtJobName.Value
ws.Cells(iRow, 2).Value = Me.txtJobNo.Value
If Me.txtJobNo.Value <> "" Then
Dim wbOutput As Workbook
Set wbOutput = Workbooks.Add
With wbOutput
.Title = "Job Number "
.Subject = "Job Number created " & Date
.Author = "Script generated"
.SaveAs Filename:=mydir & "\" & Me.txtJobNo.Value
End With
End If

'clear the data
Me.txtJobName.Value = ""
Me.txtJobNo.Value = ""
Me.txtJobNo.SetFocus
Exit Sub

End Sub

mdmackillop
10-31-2010, 05:18 AM
On which line?
Unfortunately your code can't run without the referenced workbook.

Mooseman60
10-31-2010, 05:54 AM
On which line?
Unfortunately your code can't run without the referenced workbook.

I am a novice with VBA so:help what do I need to do to reference the workbook

mdmackillop
10-31-2010, 06:55 AM
You need to post this workbook as well

Mooseman60
10-31-2010, 07:08 AM
You need to post this workbook as well

mdmackillop
10-31-2010, 07:12 AM
I meant this one.
Defined Name Lists.xls