Column Name
in [Getting Started]
|
From: Bre-x on 13 May 2010 10:09 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
From: Arvin Meyer [MVP] on 13 May 2010 10:34 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" <cholotron(a)hotmail.com> wrote in message news:eecfjXq8KHA.1436(a)TK2MSFTNGP06.phx.gbl... > 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 >
From: Bre-x on 13 May 2010 11:22 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]" <arvinm(a)mvps.invalid> wrote in message news:%23RCBolq8KHA.5900(a)TK2MSFTNGP04.phx.gbl... > 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 > >
From: Douglas J. Steele on 13 May 2010 13:30 rst.Columns.Count -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "Bre-x" <cholotron(a)hotmail.com> wrote in message news:O8ZsrAr8KHA.1892(a)TK2MSFTNGP05.phx.gbl... > 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]" <arvinm(a)mvps.invalid> wrote in message > news:%23RCBolq8KHA.5900(a)TK2MSFTNGP04.phx.gbl... >> 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 >> >> > >
From: Bre-x on 13 May 2010 13:53
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 |