This makes it closer to what I need, but it is simply
pasting the fields into the document that the form is
being run from. I need it to be run in Master List.xls.
Also, it was not proceeding to the next line everytime,
but rather typing over the entry prior to it. Please help
if you have any suggestions. I'm getting excited because
I don't have an error anymore, but I just have a little
farther to go. Thanks.


-----Original Message-----
Where is the code contained. If you are doing this in
Excel VBA, then there
is no reason to start a new instance of excel.

*******appExcel.Range("a65536").End (xlUp)*******

does nothing

appExcel.Range("a65536").End (xlUp).Select

is probably what you want.

You will get errors on all the offset lines, because the
application does
not have an offset property

selection.Offset(1,0).Value = txtContactName.Text

Would do what you want.

--
Regards,
Tom Ogilvy


"Kevin" wrote in message
...
Even though I'm pretty new at Visual Basic and basically
just cut and pasted a lot from different internet
sites, I
think my form's pretty close to working. I can get it
to
write to a different workbook, or write on the next
blank
line in the same workbook, but I'm struggling trying to
figure out how to take data from the fields and put them
into the next empty row in a different workbook. So
far I
have:

Private Sub btnSubmit_Click()
Dim appExcel As Excel.Application
Dim LastRow As Object
Set appExcel = New Excel.Application
appExcel.DisplayAlerts = False
appExcel.Visible = False
appExcel.ScreenUpdating = False
appExcel.Workbooks.Open FileName:="C:\my
documents\Sales Rep leads\Master List.xls"
appExcel.Worksheets("List").Select
*******appExcel.Range("a65536").End (xlUp)*******
appExcel.Offset(1, 0).Value =
txtContactName.Text
appExcel.Offset(1, 1).Value = txtLocation.Text
appExcel.Offset(1, 2).Value =
txtCompanyName.Text
appExcel.Offset(1, 3).Value = txtCity.Text
appExcel.Offset(1, 4).Value = txtState.Text
appExcel.Offset(1, 5).Value =
txtPhoneNumber.Text
appExcel.Offset(1, 6).Value =
txtContactName.Text
appExcel.Offset(1, 7).Value = txtNotes.Text
appExcel.ActiveWorkbook.Close Savechanges:=True
appExcel.Quit
Set appExcel = Nothing
Unload Me
End Sub


I'm pretty confident that my problem is in the line with
the astericks, but I'm not exactly sure how to fix it.
Like I said, this is a lot of cutting and pasting, but I
do want to learn so whatever you could explain to me
(both
how to fix it and what it is that I've actually done in
this whole program), would be greatly appreciated.
Thanks.


.

Private Sub btnSubmit_Click()
Dim appExcel As Excel.Application
Dim bk as Excel.Workbook
Dim rng as Excel.Range
Set appExcel = Application
appExcel.DisplayAlerts = False
appExcel.ScreenUpdating = False
set bk = appExcel.Workbooks.Open( FileName:= _
"C:\my documents\Sales Rep leads\Master List.xls")
set rng = bk.Worksheets("List").Range("a65536").End (xlUp)
rng.Offset(1, 0).Value = txtContactName.Text
rng.Offset(1, 1).Value = txtLocation.Text
rng.Offset(1, 2).Value = txtCompanyName.Text
rng.Offset(1, 3).Value = txtCity.Text
rng.Offset(1, 4).Value = txtState.Text
rng.Offset(1, 5).Value = txtPhoneNumber.Text
rng.Offset(1, 6).Value = txtContactName.Text
rng.Offset(1, 7).Value = txtNotes.Text
bk.Close Savechanges:=True
Set appExcel = Nothing
Unload Me
End Sub

It should only overwrite values if txtContactName.Text was blank and no
entry was placed in column A.

--
Regards,
Tom Ogilvy


"Kevin" wrote in message
...
This makes it closer to what I need, but it is simply
pasting the fields into the document that the form is
being run from. I need it to be run in Master List.xls.
Also, it was not proceeding to the next line everytime,
but rather typing over the entry prior to it. Please help
if you have any suggestions. I'm getting excited because
I don't have an error anymore, but I just have a little
farther to go. Thanks.


-----Original Message-----
Where is the code contained. If you are doing this in
Excel VBA, then there
is no reason to start a new instance of excel.

*******appExcel.Range("a65536").End (xlUp)*******

does nothing

appExcel.Range("a65536").End (xlUp).Select

is probably what you want.

You will get errors on all the offset lines, because the
application does
not have an offset property

selection.Offset(1,0).Value = txtContactName.Text

Would do what you want.

--
Regards,
Tom Ogilvy


"Kevin" wrote in message
...
Even though I'm pretty new at Visual Basic and basically
just cut and pasted a lot from different internet
sites, I
think my form's pretty close to working. I can get it
to
write to a different workbook, or write on the next
blank
line in the same workbook, but I'm struggling trying to
figure out how to take data from the fields and put them
into the next empty row in a different workbook. So
far I
have:

Private Sub btnSubmit_Click()
Dim appExcel As Excel.Application
Dim LastRow As Object
Set appExcel = New Excel.Application
appExcel.DisplayAlerts = False
appExcel.Visible = False
appExcel.ScreenUpdating = False
appExcel.Workbooks.Open FileName:="C:\my
documents\Sales Rep leads\Master List.xls"
appExcel.Worksheets("List").Select
*******appExcel.Range("a65536").End (xlUp)*******
appExcel.Offset(1, 0).Value =
txtContactName.Text
appExcel.Offset(1, 1).Value = txtLocation.Text
appExcel.Offset(1, 2).Value =
txtCompanyName.Text
appExcel.Offset(1, 3).Value = txtCity.Text
appExcel.Offset(1, 4).Value = txtState.Text
appExcel.Offset(1, 5).Value =
txtPhoneNumber.Text
appExcel.Offset(1, 6).Value =
txtContactName.Text
appExcel.Offset(1, 7).Value = txtNotes.Text
appExcel.ActiveWorkbook.Close Savechanges:=True
appExcel.Quit
Set appExcel = Nothing
Unload Me
End Sub


I'm pretty confident that my problem is in the line with
the astericks, but I'm not exactly sure how to fix it.
Like I said, this is a lot of cutting and pasting, but I
do want to learn so whatever you could explain to me
(both
how to fix it and what it is that I've actually done in
this whole program), would be greatly appreciated.
Thanks.


.