I need some x-pert advice :-{ .

[SunOfThundar]

Hello there all u experts. I too am new to VB6 and VBA. So woe is me!!!

Anyway, I'm sure this probably has been posted & answered,
but I tried the archives and only ended up w/ subjects etc,
that hyperlinked back to a useless homepage w/ no threads.
If one could "point" me to the answer(s), It'll me greatly appreciated. Thnx much in advance.

Problem:
I'm tryin' to capture info via text boxes from within a Excel UsrForm.

I need this captured info to be directly transferred to an Excel sheet that I've created.

My Tentative Alogorithm:
1- Capture all data that's in these txt boxes.
(Ea. txt box represents a field in my .xls sheet)
2- Open corresponding .xls sheet (via code, Unkown to user).
3- Place corresponding entries from the usr Form -> corresponding cells in .xls sheet.
4- Save the sprdsht.
**Every new record entered on the usr Form will result in a new record being added to the .xls sheet.

*** Once again, if you've read this far & U know that this has ALREADY been posted & answered, Please bear w/ me. I'm very sorry. It would be much appreciated if one could "point" me to the archived thread(s). :-}}

 

[NickISD]

Hi,
the code below is taken from a quick app I put together recently (no frills or anything) but may give you an idea of how to do it.

Hope it helps. for your code you will need to keep a track of iRow to determine the next free row to place the data.
The example below opens an existing sheet and appends new columns for club code, name and address.

Nick W

cFile = "C:\temp\myfile.xls"
Set Excel1 = CreateObject("Excel.Application&quot
With Excel1
' ----- Set Header Columns (Row 1)
.WorkBooks.Open cFile
With .ActiveWorkBook
cTemp = "Q" & iRow
.Sheets(1).Range(cTemp) = "ClubName"
cTemp = "R" & iRow
.Sheets(1).Range(cTemp) = "Manager"
cTemp = "S" & iRow
.Sheets(1).Range(cTemp) = "Address1"
cTemp = "T" & iRow
.Sheets(1).Range(cTemp) = "Address2"
cTemp = "U" & iRow
.Sheets(1).Range(cTemp) = "Address3"
cTemp = "V" & iRow
.Sheets(1).Range(cTemp) = "Town"
cTemp = "W" & iRow
.Sheets(1).Range(cTemp) = "County"
cTemp = "X" & iRow
.Sheets(1).Range(cTemp) = "Postcode"
iRow = 2
iClubCode = 1
While iClubCode > 0
cTemp = "P" & iRow
iClubCode = .Sheets(1).Range(cTemp)
If iClubCode > 0 Then
If iClubCode = 5820 Then iClubCode = 5526
csql = "SELECT name,attention,add1,add2,add3,town,county,postcode "
csql = csql & "FROM address WHERE code = '" & iClubCode & "'"
iStat = Db.OpenADOResultSet(rs, csql, adOpenForwardOnly)
If iStat = True Then
If Not (rs.EOF And rs.BOF) Then
cTemp = "Q" & iRow
.Sheets(1).Range(cTemp) = StrDeNull(rs!Name)
cTemp = "R" & iRow
.Sheets(1).Range(cTemp) = StrDeNull(rs!attention)
cTemp = "S" & iRow
.Sheets(1).Range(cTemp) = StrDeNull(rs!add1)
cTemp = "T" & iRow
.Sheets(1).Range(cTemp) = StrDeNull(rs!add2)
cTemp = "U" & iRow
.Sheets(1).Range(cTemp) = StrDeNull(rs!add3)
cTemp = "V" & iRow
.Sheets(1).Range(cTemp) = StrDeNull(rs!town)
cTemp = "W" & iRow
.Sheets(1).Range(cTemp) = StrDeNull(rs!county)
cTemp = "X" & iRow
.Sheets(1).Range(cTemp) = StrDeNull(rs!postcode)
Else
iNoMatch = iNoMatch + 1
End If
rs.Close
End If
Else
iNoMatch = iNoMatch + 1
End If

iRow = iRow + 1
Wend

' ----- Save Work Sheet
.Close SaveChanges:=True
End With
.Quit
End With
Set Excel1 = Nothing