Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way to get data from Access 2003 to Excel 2003
I have two Access queries set up for two tables in an employee database at a
network location. Each prompts for an employee ID and returns one number - a sum of the employee's data in the particular table. With up to 20 Employees in each spreadsheet, we could import up to 40 sums (presently 40 separate queries). The data will not end up in an Excel table. In Excel each employee's info is in an area 25 rows x 15 columns on the same sheet, with the employee ID and two pieces of imported data in the same relative cells in each of the 20 employee areas. This import will be done daily as part of a macro and formulas processing purely Excel data. This is my first foray into importing data into Excel from Access. What is the best way to get this info into those Excel cells? Importing the data into a separate Excel table having the employee ID as the leftmost field, then using formulas in those 40 cells to grab the applicable data from the table? Importing the data directly into those 40 cells through an Excel macro? Another way? So far the "importing data" help I have found has been mostly getting data directly from an Access table to an Excel table, and not through queries that return info as I described in the first paragraph. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way to get data from Access 2003 to Excel 2003
Try the below.. For connection strings you can refer
http://www.connectionstrings.com/ Sub ExtractFromAccess() Dim strDB As String, lngRow As Long Dim con As New ADODB.Connection, rs As New ADODB.Recordset strDB = "C:\opstats.mdb" con.Open "DBQ=" & strDB & ";Driver={Microsoft Access Driver (*.mdb)}" rs.CursorLocation = adUseClient rs.Open "select * from <tablename", con, adOpenDynamic '--------------------------------- lngRow = 1 Do While rs.EOF = False Range("A" & lngRow) = rs("opid") Range("B" & lngRow) = rs("opname") lngRow = lngRow + 1 rs.MoveNext Loop '--------------------------------- rs.Close: con.Close Set rs = Nothing: Set con = Nothing End Sub If this post helps click Yes --------------- Jacob Skaria "AZSteve" wrote: I have two Access queries set up for two tables in an employee database at a network location. Each prompts for an employee ID and returns one number - a sum of the employee's data in the particular table. With up to 20 Employees in each spreadsheet, we could import up to 40 sums (presently 40 separate queries). The data will not end up in an Excel table. In Excel each employee's info is in an area 25 rows x 15 columns on the same sheet, with the employee ID and two pieces of imported data in the same relative cells in each of the 20 employee areas. This import will be done daily as part of a macro and formulas processing purely Excel data. This is my first foray into importing data into Excel from Access. What is the best way to get this info into those Excel cells? Importing the data into a separate Excel table having the employee ID as the leftmost field, then using formulas in those 40 cells to grab the applicable data from the table? Importing the data directly into those 40 cells through an Excel macro? Another way? So far the "importing data" help I have found has been mostly getting data directly from an Access table to an Excel table, and not through queries that return info as I described in the first paragraph. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way to get data from Access 2003 to Excel 2003
Just to add on
--You need to either Refer to 'Microsoft ActiveX Data Object 2.x library' from VBEToolsReferences OR reference the object using CreateObject Function at the beginning of the code Set con = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") --You can either use the field names to retrieve data like Range("A" & lngRow) = rs("opid") Range("B" & lngRow) = rs("opname") OR use the index number Range("A" & lngRow) = rs(0) Range("B" & lngRow) = rs(1) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try the below.. For connection strings you can refer http://www.connectionstrings.com/ Sub ExtractFromAccess() Dim strDB As String, lngRow As Long Dim con As New ADODB.Connection, rs As New ADODB.Recordset strDB = "C:\opstats.mdb" con.Open "DBQ=" & strDB & ";Driver={Microsoft Access Driver (*.mdb)}" rs.CursorLocation = adUseClient rs.Open "select * from <tablename", con, adOpenDynamic '--------------------------------- lngRow = 1 Do While rs.EOF = False Range("A" & lngRow) = rs("opid") Range("B" & lngRow) = rs("opname") lngRow = lngRow + 1 rs.MoveNext Loop '--------------------------------- rs.Close: con.Close Set rs = Nothing: Set con = Nothing End Sub If this post helps click Yes --------------- Jacob Skaria "AZSteve" wrote: I have two Access queries set up for two tables in an employee database at a network location. Each prompts for an employee ID and returns one number - a sum of the employee's data in the particular table. With up to 20 Employees in each spreadsheet, we could import up to 40 sums (presently 40 separate queries). The data will not end up in an Excel table. In Excel each employee's info is in an area 25 rows x 15 columns on the same sheet, with the employee ID and two pieces of imported data in the same relative cells in each of the 20 employee areas. This import will be done daily as part of a macro and formulas processing purely Excel data. This is my first foray into importing data into Excel from Access. What is the best way to get this info into those Excel cells? Importing the data into a separate Excel table having the employee ID as the leftmost field, then using formulas in those 40 cells to grab the applicable data from the table? Importing the data directly into those 40 cells through an Excel macro? Another way? So far the "importing data" help I have found has been mostly getting data directly from an Access table to an Excel table, and not through queries that return info as I described in the first paragraph. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way to get data from Access 2003 to Excel 2003
Jacob: This is what I have so far from your suggestion
Sub ExtractFromAccess() Set con = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") Dim strDB As String, strQ1 as String, lngRow As Long strDB = "\\phepsilon\groups\NOCC_Restricted\Administration \NOCC Employee File.mdb" strQ1 = "13-Week Points for CSR" 'The following is the SQL from strQ1 'SELECT DISTINCT Sum([Points]) AS SumofPoints 'FROM Absences 'WHERE (((Absences.Date)Date()-91) AND ((Absences.[Employee Number])=[Enter Employee Number])); con.Open "DBQ=" & strDB & ";Driver={Microsoft Access Driver (*.mdb)}" rs.CursorLocation = adUseClient rs.Open "select * from <tablename", con, adOpenDynamic '--------------------------------- lngRow = 1 Do While rs.EOF = False Range("A" & lngRow) = rs("opid") Range("B" & lngRow) = rs("opname") lngRow = lngRow + 1 rs.MoveNext Loop '--------------------------------- rs.Close: con.Close Set rs = Nothing: Set con = Nothing End Sub ============================ At "rs.CursorLocation = adUseClient" I am getting error message "Arguments are of the wrong type, are out of the acceptable range, or are in conflict with one another." What is the issue here? As I had said, I am using a Query in the DB (called strQ1 above, with the actual SQL of the query just below it). Instead of referencing a specific Query do I have instead have to put the actual SQL from that Query in the next line where you have rs.Open "select * from <tablename", con, adOpenDynamic ? Obviously I will have to reference an employee number rather than prompting for it. Is this code putting the results from all employee numbers I reference into an Excel table at A1, B1, A2, B2, etc based on lngRow? Thanks for your help. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way to get data from Access 2003 to Excel 2003
Refer to 'Microsoft ActiveX Data Object 2.x library' from
VBEToolsReferences and try the below...For testing keep your database in c:\ Sub ExtractFromAccess() Dim strDB As String, lngRow As Long, varEmpNo As Variant Dim con As New ADODB.Connection, rs As New ADODB.Recordset strDB = "C:\db1.mdb" varEmpno = <query employee number con.Open "DBQ=" & strDB & ";Driver={Microsoft Access Driver (*.mdb)}" rs.CursorLocation = adUseClient strQuery = "SELECT Sum(points) AS SumofPoints FROM Absences WHERE " & _ "[Absences.Date] Date() - 91 And [Absences.Employee Number] = " & varEmpNo rs.Open strQuery, con, adOpenDynamic Do While rs.EOF = False Msgbox rs(0) rs.MoveNext Loop rs.Close: con.Close Set rs = Nothing: Set con = Nothing End Sub If this post helps click Yes --------------- Jacob Skaria "AZSteve" wrote: Jacob: This is what I have so far from your suggestion Sub ExtractFromAccess() Set con = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") Dim strDB As String, strQ1 as String, lngRow As Long strDB = "\\phepsilon\groups\NOCC_Restricted\Administration \NOCC Employee File.mdb" strQ1 = "13-Week Points for CSR" 'The following is the SQL from strQ1 'SELECT DISTINCT Sum([Points]) AS SumofPoints 'FROM Absences 'WHERE (((Absences.Date)Date()-91) AND ((Absences.[Employee Number])=[Enter Employee Number])); con.Open "DBQ=" & strDB & ";Driver={Microsoft Access Driver (*.mdb)}" rs.CursorLocation = adUseClient rs.Open "select * from <tablename", con, adOpenDynamic '--------------------------------- lngRow = 1 Do While rs.EOF = False Range("A" & lngRow) = rs("opid") Range("B" & lngRow) = rs("opname") lngRow = lngRow + 1 rs.MoveNext Loop '--------------------------------- rs.Close: con.Close Set rs = Nothing: Set con = Nothing End Sub ============================ At "rs.CursorLocation = adUseClient" I am getting error message "Arguments are of the wrong type, are out of the acceptable range, or are in conflict with one another." What is the issue here? As I had said, I am using a Query in the DB (called strQ1 above, with the actual SQL of the query just below it). Instead of referencing a specific Query do I have instead have to put the actual SQL from that Query in the next line where you have rs.Open "select * from <tablename", con, adOpenDynamic ? Obviously I will have to reference an employee number rather than prompting for it. Is this code putting the results from all employee numbers I reference into an Excel table at A1, B1, A2, B2, etc based on lngRow? Thanks for your help. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way to get data from Access 2003 to Excel 2003
If you are looking at executing without referencing the library...in the
previous code replace the below two lines rs.CursorLocation = adUseClient rs.Open "select * from <tablename", con, adOpenDynamic with rs.CursorLocation = 3 rs.Open "select * from <tablename", con, 2 If this post helps click Yes --------------- Jacob Skaria "AZSteve" wrote: Jacob: This is what I have so far from your suggestion Sub ExtractFromAccess() Set con = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") Dim strDB As String, strQ1 as String, lngRow As Long strDB = "\\phepsilon\groups\NOCC_Restricted\Administration \NOCC Employee File.mdb" strQ1 = "13-Week Points for CSR" 'The following is the SQL from strQ1 'SELECT DISTINCT Sum([Points]) AS SumofPoints 'FROM Absences 'WHERE (((Absences.Date)Date()-91) AND ((Absences.[Employee Number])=[Enter Employee Number])); con.Open "DBQ=" & strDB & ";Driver={Microsoft Access Driver (*.mdb)}" rs.CursorLocation = adUseClient rs.Open "select * from <tablename", con, adOpenDynamic '--------------------------------- lngRow = 1 Do While rs.EOF = False Range("A" & lngRow) = rs("opid") Range("B" & lngRow) = rs("opname") lngRow = lngRow + 1 rs.MoveNext Loop '--------------------------------- rs.Close: con.Close Set rs = Nothing: Set con = Nothing End Sub ============================ At "rs.CursorLocation = adUseClient" I am getting error message "Arguments are of the wrong type, are out of the acceptable range, or are in conflict with one another." What is the issue here? As I had said, I am using a Query in the DB (called strQ1 above, with the actual SQL of the query just below it). Instead of referencing a specific Query do I have instead have to put the actual SQL from that Query in the next line where you have rs.Open "select * from <tablename", con, adOpenDynamic ? Obviously I will have to reference an employee number rather than prompting for it. Is this code putting the results from all employee numbers I reference into an Excel table at A1, B1, A2, B2, etc based on lngRow? Thanks for your help. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way to get data from Access 2003 to Excel 2003
Based on previous suggestions from Jacob Skaria and others around 9/29, I
have done this but still get errors. Sub Access_Data() 'Requires reference to Microsoft ActiveX Data Objects xx Library Dim Cn As ADODB.Connection, rs As ADODB.Recordset Dim MyConn, sSQL As String Dim Rw As Long, Col As Long, c As Long Dim MyField, Location As Range, Here As Range Dim varEmplNo As String 'Set destination Set Location = [V3] 'Here = Range("V3").Address 'Who = Range("V3").Offset(0, -5).Value varEmplNo = "400476" 'Set source MyConn = "\\phepsilon\groups\NPIC_Restricted\Administration \NOOC Employee File.mdb" 'Create query sSQL = "SELECT DISTINCT Sum([Points]) AS SumofPoints FROM Absences WHERE (((Absences.Date)Date()-91) AND ((Absences.[Employee Number])= " & varEmplNo & "));" 'Create RecordSet Set Cn = New ADODB.Connection With Cn .Provider = "Microsoft.Jet.OLEDB.4.0" .Open MyConn Set rs = .Execute(sSQL) End With (etc) Gives "Data type mismatch in criteria expression" with the "Set rs = " line highlighted. I do have the reference specified at the beginning in the Reference library. --------------------- Sub ExtractFromAccess() Dim strDB As String, lngRow As Long, varEmpNo As Variant Dim con As New ADODB.Connection, rs As New ADODB.Recordset strDB = "C:\Documents and Settings\mullst\Desktop\NOOC Employee File.mdb" varEmpNo = "400476" con.Open "DBQ=" & strDB & ";Driver={Microsoft Access Driver (*.mdb)}" rs.CursorLocation = adUseClient strQuery = "SELECT Sum(points) AS SumofPoints FROM Absences WHERE [Absences.Date] Date() - 91 And [Absences.Employee Number] = " & varEmpNo rs.Open strQuery, con, adOpenDynamic Do While rs.EOF = False MsgBox rs(0) rs.MoveNext Loop rs.Close: con.Close Set rs = Nothing: Set con = Nothing End Sub Gives "[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression" with "rs.Open strQuery, con, adOpenDynamic" highlighted. ---------------------------- I have tried lots of minor variations and still get error messages. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
importing/linking data from an Access 2003 Query to an Excel 2003 | Excel Discussion (Misc queries) | |||
opening excel files from access 2003, win xp, office 2003 | Excel Discussion (Misc queries) | |||
Link Excel 2003 named range to Access 2003 Table | Excel Programming | |||
Transferring Large Amount of Data from Excel 2003 to Access 2003 | Excel Programming | |||
Data Access Pages - Excel 2003 | Excel Discussion (Misc queries) |