Column Name

  • Thread starter Thread starter Bre-x
  • Start date Start date
B

Bre-x

Hi

I am using this code to send data into excel

Set rst = CurrentDb.OpenRecordset("SELECT Temp.* FROM Temp;")
rst.MoveFirst
Do While Not rst.EOF
objSht.Cells(iRow, 1).Value = rst!main_id
objSht.Cells(iRow, 2).Value = rst!year_id
iRow = iRow + 1
rst.MoveNext
Loop
rst.Close
End With

But how would I modify my code if I dont know the name of the columns?

objSht.Cells(iRow, 1).Value = rst!column1
objSht.Cells(iRow, 1).Value = rst!column2

objSht.Cells(iRow, 1).Value = rst!(1)
objSht.Cells(iRow, 1).Value = rst!(2)

Thank you all

Bre-x
 
In your example, it is the first column and second column, so:

objSht.Cells(iRow, 1).Value = rst!column1
objSht.Cells(iRow, 1).Value = rst!column2

should be:

objSht.Cells(iRow, 1).Value = rst!column1
objSht.Cells(iRow, 2).Value = rst!column2
 
Thank you, Now how could I find out how many columns are there?

If rst!column3 does not exits the code stop executing: "Item not found in
this collection"
 
Thanks for your answer.
I must be missing something: "Method or Data member not found"

------------------------------------------------------------------


Dim var_sql As String
Dim Responce
Dim the_year
Dim myc As Integer

Dim objXL As Object
Dim rst As DAO.Recordset
Dim iRow As Integer, iRow2 As Integer

Responce = InputBox("Enter Year :", " One Time Donations", Format(Now(),
"YYYY"))

If Responce = "" Then
Responce = MsgBox("Cancel By User. ", vbCritical, " Sunset")
Exit Function
End If

the_year = CInt(Responce)
var_sql = "SELECT tmain.main_id, tmain.payer_id, tsettings.sett_id,
tmain.one_time FROM tsettings INNER JOIN (tmain INNER JOIN tmembers ON
tmain.payer_id = tmembers.mem_id) ON tsettings.sett_value = tmain.main_desc
WHERE (((tmain.main_desc)<>'One Time Donation') AND ((tmain.one_time)<>0)
AND ((tmain.year_id)=" & the_year & ")) ORDER BY tmain.main_id;"
CurrentDb.QueryDefs("sys_donations").SQL = var_sql

DoCmd.SetWarnings False
DoCmd.OpenQuery "sys_donations_temp"
DoCmd.SetWarnings True


Set objXL = CreateObject("Excel.Application")
With objXL.Application
.Visible = True
.Workbooks.Open Application.CurrentProject.Path &
"\Excel\don_other.xls"
.ActiveWindow.WindowState = xlMinimized
Set objSht = objXL.Worksheets("Main")
With objSht
.Range("A6:O600").ClearContents
'Lot Labels
.Range("J5") = DLookup("[sett_value]", "tsettings",
"[sett_id]=27")
.Range("K5") = DLookup("[sett_value]", "tsettings",
"[sett_id]=28")
.Range("L5") = DLookup("[sett_value]", "tsettings",
"[sett_id]=29")
.Range("M5") = DLookup("[sett_value]", "tsettings",
"[sett_id]=30")
.Range("N5") = DLookup("[sett_value]", "tsettings",
"[sett_id]=31")

iRow = 6
iRow2 = 3
Set rst = CurrentDb.OpenRecordset("SELECT Temp.* FROM
Temp;")
myc = rst.Columns.Count

rst.MoveFirst
Do While Not rst.EOF
objSht.Cells(iRow, 1).Value = rst!mem_last
objSht.Cells(iRow, 2).Value = rst!mem_firts
objSht.Cells(iRow, 3).Value = rst!mem_street
objSht.Cells(iRow, 4).Value = rst!mem_city
objSht.Cells(iRow, 5).Value = rst!mem_prov
objSht.Cells(iRow, 6).Value = rst!mem_pc
objSht.Cells(iRow, 7).Value = rst!mem_phone
objSht.Cells(iRow, 8).Value = rst!church
objSht.Cells(iRow, 9).Value = rst!main_id
objSht.Cells(iRow, 10).Value = rst(11)
objSht.Cells(iRow, 11).Value = rst(12)
objSht.Cells(iRow, 12).Value = rst(13)
If myc > 14 Then
objSht.Cells(iRow, 12).Value = rst(14)
End If
objSht.Cells(iRow, 13).Value = rst(15)
If myc > 15 Then

End If
iRow = iRow + 1
rst.MoveNext
Loop
rst.Close
End With
.ActiveWindow.WindowState = xlMaximized
End With
Set objSht = Nothing
Set objXL = Nothing

End Function


Public Function don_onetime()
On Error GoTo Err_don_onetime

Dim objXL As Object
Dim rst As DAO.Recordset
Dim iRow As Integer, iRow2 As Integer
Dim Responce
Dim the_year

Responce = InputBox("Enter Year :", " One Time Donations", Format(Now(),
"YYYY"))

If Responce = "" Then
Responce = MsgBox("Cancel By User. ", vbCritical, " Sunset")
Exit Function
End If

the_year = CInt(Responce)

DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT tmain.main_id, tmain.year_id, tmain.main_date,
[mem_firts] & ' ' &
[mem_last] AS payee, tmain.main_type, tmain.main_desc, tmain.one_time INTO
Temp FROM
tmain INNER JOIN tmembers ON tmain.payer_id = tmembers.mem_id
WHERE (((tmain.year_id) = " & the_year & ") And ((tmain.main_desc) =
'One Time Donation') And ((tmain.one_time) <> 0)) ORDER BY tmain.main_id;"
DoCmd.SetWarnings True

Set objXL = CreateObject("Excel.Application")
With objXL.Application
.Visible = True
.Workbooks.Open Application.CurrentProject.Path &
"\Excel\don_onetime.xls"
.ActiveWindow.WindowState = xlMinimized
Set objSht = objXL.Worksheets("Main")
With objSht
.Range("A6:G600").ClearContents
iRow = 6
iRow2 = 3
Set rst = CurrentDb.OpenRecordset("SELECT Temp.* FROM
Temp;")
rst.MoveFirst
Do While Not rst.EOF
objSht.Cells(iRow, 1).Value = rst!main_id
objSht.Cells(iRow, 2).Value = rst!year_id
objSht.Cells(iRow, 3).Value = rst!main_date
objSht.Cells(iRow, 4).Value = rst!payee
objSht.Cells(iRow, 5).Value = rst!main_desc
objSht.Cells(iRow, 6).Value = rst!one_time
iRow = iRow + 1
rst.MoveNext
Loop
rst.Close
End With
.ActiveWindow.WindowState = xlMaximized
End With
Set objSht = Nothing
Set objXL = Nothing
Exit_don_onetime:
Exit Function
Err_don_onetime:
MsgBox Err.Description & " " & Err.Number
Resume Exit_don_onetime
 
Back
Top