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
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"Bre-x" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

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"



"Arvin Meyer [MVP]" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
> --
> Arvin Meyer, MCP, MVP
> http://www.datastrat.com
> http://www.accessmvp.com
> http://www.mvps.org/access
>
>

rst.Columns.Count

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"Bre-x" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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"
>
>
>
> "Arvin Meyer [MVP]" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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
>> --
>> Arvin Meyer, MCP, MVP
>> http://www.datastrat.com
>> http://www.accessmvp.com
>> http://www.mvps.org/access
>>
>>
>
>

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

Sorry: mental lapse. It should have been

rst.Fields.Count


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"Bre-x" <(E-Mail Removed)> wrote in message
news:OS6e$(E-Mail Removed)...
> Thanks for your answer.
> I must be missing something: "Method or Data member not found"

thank you!!!!!

Everything is working now!!!


"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote in message
news:(E-Mail Removed)...
> Sorry: mental lapse. It should have been
>
> rst.Fields.Count
>
>
> --
> Doug Steele, Microsoft Access MVP
> http://www.AccessMVP.com/DJSteele
> (no e-mails, please!)
>
> "Bre-x" <(E-Mail Removed)> wrote in message
> news:OS6e$(E-Mail Removed)...
>> Thanks for your answer.
>> I must be missing something: "Method or Data member not found"
>
>