Solved: Excel to access cell for cell import

[White_Nova]

Thanks, and once the queries are ready in Access how do i bring the specific ones through to Excel?

[White_Nova]

Alright, i have read through the website provided, very interesting...

Below is the code im tring to use to pull the info back to Excel...
Its very basic cause im not too sure...

[VBA]Dim oConn As Object
Dim oRS As Object
Dim sSQL As String


Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= C:\Documents and Settings\Desktop\Database Info\Sales Telly Store.mdb"

SQL SELECT * from Query.Bookings



'Bookings
SELECT Data.TellyTeam, Count(Data.TellyTeam) AS CountOfTellyTeam
from Data
GROUP BY Data.TellyTeam;
'Confirmations
SELECT Data.TellyTeam, Count(Data.ConfCode) AS CountOfConfCode
from Data
GROUP BY Data.TellyTeam;
'Deals
SELECT Data.TellyTeam, Count(Data.Sale) AS CountOfSale
from Data
WHERE ((([Data]![Sale]) = "Y"))
GROUP BY Data.TellyTeam;
'Shows
SELECT Data.TellyTeam, Count(Data.AgtCode) AS CountOfAgtCode
from Data
GROUP BY Data.TellyTeam;

oConn.Execute sSQL
oConn.Close
Set oConn = Nothing[/VBA]

[Charlize]

A wild guess that it's not doing what you want it to do. Take a look at my post where you were able to pull back all the data from a table. Along those lines (in green) is a small example of a where clause when mydate = 08/11/2007. If you want to see the bookings that are confirmed you'll have to query the database for the field confirmed to Y (if it's the field confirmed).

Group By is used to group by a field. So if you got some salespersons you can group by the field salesperson.

And every sql query that's doing something different has to be executed on his own (Deals ??? what's that)

If you explain in plain english what you want, cause I'm afraid that this coding will not work (I could be wrong off course).

[White_Nova]

We have a telemarketing department that phones people and they come in for a presentation, they then get allocated to a salesperson who does or does not write a sale (Deal) from that person...

Tried the old VBA you sent

[VBA]Sub GetData()
'You have to set a reference to
'microsoft dao 3.6 object library
'Tested under excel 2003
Dim Db As Database
Dim Rs As Recordset
Dim Ws As Object
Dim i As Long
Dim MyPath As String

Set Ws = Worksheets(2)
'Set the Path to the database. This line is useful because
'if your database is in another location, you just need to change
'it here and the Path Variable will be used throughout the code
MyPath = "C:\Documents and Settings\Russell\Desktop\Database Info\Sales Telly Store.mdb"
'This set of code will activate Sheet3 and clear any existing data
'After clearing the data it will select cell A1
Ws.Activate
Range("A1").Activate
Selection.CurrentRegion.Select
Selection.ClearContents
Range("A1").Select
'Set the Database, and RecordSet This Table exists in the database
Set Db = Workspaces(0).OpenDatabase(MyPath, ReadOnly:=True)
'This will set the RecordSet to all records in the Store table
Set Rs = Db.OpenRecordset("Bookings")
'Set Rs = _
'Db.OpenRecordset("SELECT * FROM Store WHERE Mydate = '08/11/2007';")
'This loop will collect the field names and place them in the first
'row starting at "A1"
For i = 0 To Rs.Fields.Count - 1
Ws.Cells(1, i + 1).Value = Rs.Fields(i).Name
Next i
'The next line simply formats the headers to bold font
Ws.Range(Ws.Cells(1, 1), Ws.Cells(1, Rs.Fields.Count)).Font.Bold = True
'The next line will get the data from the recordset and copy it
'into the Worksheet (Sheet1).
Ws.Range("A2").CopyFromRecordset Rs
'This next code set will just select the data region and
'auto-fit the columns
Ws.Range("A1").Select
Selection.CurrentRegion.Select
Selection.Columns.AutoFit
Ws.Range("A1").Select
Rs.Close
Db.Close
End Sub[/VBA]

But is come up with a missmatch error.

I have created quesries in Access that already answer the template i have, i would just like to bring them back to excel...

Make sense???

[White_Nova]

Hey there

I have ammeneded the code and think it will work but it gives me an error Run Time Error 13 a data type mismatch...

[VBA]Sub GetData()
'You have to set a reference to
'microsoft dao 3.6 object library
'Tested under excel 2003
Dim Db As Database
Dim Rs As Recordset
Dim Ws As Object
Dim i As Long
Dim MyPath As String

Set Ws = Worksheets(2)
'Set the Path to the database. This line is useful because
'if your database is in another location, you just need to change
'it here and the Path Variable will be used throughout the code
MyPath = "C:\Documents and Settings\Russell\Desktop\Database Info\Sales Telly Store.mdb"
'This set of code will activate Sheet3 and clear any existing data
'After clearing the data it will select cell A1
Ws.Activate
Range("A1").Activate
Selection.CurrentRegion.Select
Selection.ClearContents
Range("A1").Select
'Set the Database, and RecordSet This Table exists in the database
Set Db = Workspaces(0).OpenDatabase(MyPath, ReadOnly:=True)

sSQL = "SELECT" * "Data.TellyTeam, Count(Data.AgtCode) AS CountOfAgtCode FROM Data GROUP BY Data.TellyTeam;"
sSQL = "SELECT Data.TellyTeam, Count(Data.[NQ/Cat]) AS [CountOfNQ/Cat] FROM Data WHERE (([Data]![NQ/Cat] = Qual Or (Data.[NQ/Cat]) = QWOS Or (Data.[NQ/Cat]) = OF))GROUP BY Data.TellyTeam ORDER BY Data.TellyTeam;"
sSQL = "SELECT Data.TellyTeam, Count(Data.AgtCode) AS CountOfAgtCode FROM Data GROUP BY Data.TellyTeam;"
sSQL = "SELECT Data.TellyTeam, Count(Data.Sale) AS CountOfSale FROM Data WHERE ((([Data]![Sale]) = Y)) GROUP BY Data.TellyTeam;"
sSQL = "SELECT Data.TellyTeam, Count(Data.ConfCode) AS CountOfConfCode FROM Data GROUP BY Data.TellyTeam;"
sSQL = "SELECT Data.TellyTeam, Count(Data.TellyTeam) AS CountOfTellyTeam FROM Data GROUP BY Data.TellyTeam;"

'This next code set will just select the data region and
'auto-fit the columns
Ws.Range("A1").Select
Selection.CurrentRegion.Select
Selection.Columns.AutoFit
Ws.Range("A1").Select
Rs.Close
Db.Close
End Sub
[/VBA]

Please help....

[Charlize]

Give a sanitized version of your acces database and a layout of the result you wish to achieve.

- Even if you use 100 sql = statements, only the last one will be executed if you give the command

and you better use [VBA]Db.OpenRecordset("SELECT * FROM Data WHERE Sale = 'Y';")[/VBA]and Data is the name of your table in the .mdb database.

By the way, your name is Russell ??? If not, it will not work for the path.

[White_Nova]

Arent you on MSN so i can send you the database so you can see what im looking for?
Could speed things up just a little...

FYI - found this code which seems to do the trick

[VBA]Dim SQL As String
Dim filenm As String
'Bookings
SQL = "Select * From Bookings"
' "C:\Documents and Settings\Bruce\My Documents\Databases\All Of Access\Ians Way.mdb"
filenm = (ActiveWorkbook.Path & "\Sales Telly Store.mdb")
Call GetCn(adoconn, adors, SQL, filenm, "", "")

Dim xlSht As Excel.Worksheet
Set xlSht = Sheets("Sheet2")
xlSht.range("B2").CopyFromRecordset adors
adors.Close
adoconn.Close
Set adors = Nothing
Set adoconn = Nothing
Set xlSht = Nothing

'Confirmations
SQL = "Select * From Confirmations"
' "C:\Documents and Settings\Bruce\My Documents\Databases\All Of Access\Ians Way.mdb"
filenm = (ActiveWorkbook.Path & "\Sales Telly Store.mdb")
Call GetCn(adoconn, adors, SQL, filenm, "", "")

Set xlSht = Sheets("Sheet2")
xlSht.range("D2").CopyFromRecordset adors
adors.Close
adoconn.Close
Set adors = Nothing
Set adoconn = Nothing
Set xlSht = Nothing
'Shows
SQL = "Select * From Shows"
' "C:\Documents and Settings\Bruce\My Documents\Databases\All Of Access\Ians Way.mdb"
filenm = (ActiveWorkbook.Path & "\Sales Telly Store.mdb")
Call GetCn(adoconn, adors, SQL, filenm, "", "")

Set xlSht = Sheets("Sheet2")
xlSht.range("F2").CopyFromRecordset adors
adors.Close
adoconn.Close
Set adors = Nothing
Set adoconn = Nothing
Set xlSht = Nothing
End Sub[/VBA]

[Charlize]

When you've got some queries defined in Access. Maybe try this one. Don't promise a thing but you could try.[VBA]Sub GetQueryDef()
'This sub will get data from an Existing QueryDef in
'database and place the data on sheet2.
Dim Db As Database
Dim Qd As QueryDef
Dim Rs As Recordset
Dim Ws As Worksheet
Dim i As Long
Dim Path As String
'Set the Path to the database
Path = "C:\yourfiledirectory\Sales and whatever.mdb"
'Set Ws
Set Ws = Sheets("Sheet2")

Ws.Activate
Range("A1").Activate
Selection.CurrentRegion.Select
Selection.ClearContents
Range("A1").Select
'Set the Database and QueryDef. This QueryDef exists in the
'database.
Set Db = Workspaces(0).OpenDatabase(Path, ReadOnly:=True, _
Exclusive:=False)
'replace the name of your query with the real name
Set Qd = Db.QueryDefs("The name of your query")
'Create a new Recordset from the Query based on the stored
'QueryDef.
Set Rs = Qd.OpenRecordset()
'This loop will collect the field names and place them in the first
'row starting at "A1."
For i = 0 To Rs.Fields.Count - 1
Ws.Cells(1, i + 1).Value = Rs.Fields(i).Name
Next i
'This line simply sets the font to bold for the headers.
Ws.Range(Ws.Cells(1, 1), Ws.Cells(1, Rs.Fields.Count)).Font.Bold = True
'The next line will get the data from the recordset and copy it
'into the Worksheet (Sheet2).
Ws.Range("A2").CopyFromRecordset Rs
'This next code set will just select the data region and auto-fit
'the columns
Ws.Select
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Columns.AutoFit
Range("A1").Select
Qd.Close
Rs.Close
Db.Close
End Sub[/VBA]

[White_Nova]

sorted with that one thanks!!!!!

I want to transfer a large amount of data back to my dtabase but i want to ammend the current data in there..

Code im trying is below

[VBA]Sub Transfer()

Dim oConn As Object
Dim oRS As Object
Dim sSQL As String


Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= C:\Documents and Settings\Russell\Desktop\Database info\Sales Telly Store.mdb"

sSQL = "INSERT INTO Store (Uni again, Uni, Agtname, AgtCode, Surname, SessDate, Day, Session,LeadNo, Timein, weeknum, Income, Suburb, Age, Race, M, Sale, SOLID, SUSPENSIVE, DSD, Uvongo, QVCFinite, Units, ContrNumber, ManName, ManCode, DoorName, DoorCode, SemName, SemCode, TellyOp, Telycode, ConfName, ConfCode, TellyTeam, Prize1, Prize2, Prize3, Chosennumber, Actualnumber, Timeout, Lead count, Income average, Average inc, AV, AV2, 30-35, 35-40, 40-45, 1, 12, 13, AV3, Door, Sem, Man Alloc, Qual, NQ/Cat)" & _
"VALUES ('" & [A2].Text & "','" & [B2].Text & "','" & [C2].Text & _
"','" & [D2].Text & "','" & [E2].Text & "','" & [F2].Text & _
"','" & [G2].Text & "','" & [H2].Text & "','" & [I2].Text & _
"','" & [J2].Text & "','" & [K2].Text & "','" & [L2].Text & _
"','" & [M2].Text & "','" & [N2].Text & "','" & [O2].Text & _
"','" & [P2].Text & "','" & [Q2].Text & "','" & [R2].Text & _
"','" & [S2].Text & "','" & [T2].Text & "','" & [U2].Text & _
"','" & [V2].Text & "','" & [W2].Text & "','" & [X2].Text & _
"','" & [Y2].Text & "','" & [Z2].Text & "','" & [AA2].Text & _
"','" & [AB2].Text & "','" & [AC2].Text & "','" & [AD2].Text & _
"','" & [AE2].Text & "','" & [AF2].Text & "','" & [AG2].Text & _
"','" & [AH2].Text & "','" & [AI2].Text & "','" & [AJ2].Text & _
"','" & [AK2].Text & "','" & [AL2].Text & "','" & [AM2].Text & _
"','" & [AN2].Text & "','" & [AO2].Text & "','" & [AP2].Text & _
"','" & [AQ2].Text & "','" & [AR2].Text & "','" & [AS2].Text & _
"','" & [AT2].Text & "','" & [AU2].Text & "','" & [AV2].Text & _
"','" & [AW2].Text & "','" & [AX2].Text & "','" & [AY2].Text & _
"','" & [AZ2].Text & "','" & [BA2].Text & "','" & [BB2].Text & _
"','" & [BC2].Text & "','" & [BD2].Text & "','" & [BE2].Text & _
"','" & [BF2].Text & "')"
oConn.Execute sSQL
oConn.Close
Set oConn = Nothing
End Sub[/VBA]

Please help again!!!!
Your an angel...

[Charlize]

I'll give you a hint for this one. But you better use a sample database for trial and error. The general idea is this

UPDATE table_name SET column_name = new_value WHERE column_name = some_value

[vba]UPDATE Bookings SET Paid = "Y" WHERE Confirmed = "Y", Name = "White_Nova"[/vba]to replace data in a table. But you also need to check if there is more than one record that matches the where clausule.

So first check your database with the where clausule and if the record set is one (count them in your excel sheet), you're in business. If not, you'll need an additional qualifier to get a unique (single) record.

[White_Nova]

Hi Charlize

Maybe getting carried away with the explination... lets try again...

I have an access database with a "Data" table, off that i am running queries whic i then bring into Excel...

The above is working 100%
The part i need help with is just updating new data into the "Data" table in access from Excel (there are 58 colombs of data)

Is this possible and if so how?

Thanks agian for all your help...

[Charlize]

Hi Charlize

Maybe getting carried away with the explination... lets try again...

I have an access database with a "Data" table, off that i am running queries whic i then bring into Excel...

The above is working 100%

Off course it's working.

The part i need help with is just updating new data into the "Data" table in access from Excel (there are 58 colombs of data)

Is this possible and if so how?

Yes it's possible. Have you read my answer regarding the UPDATE thing. You'll have to use that instead of INSERT INTO. You can use the same coding as the INSERT INTO coding but you'll have to change the sql string to a UPDATE coding.

Try something, please. If you post your coding, I'll give you a working sample (yes, it's already coded and tested and it works like a charm. At least for my testdatabase - MyId, MyManager, MyDate, MySession).

[White_Nova]

Hi Charlize

Managed to fiddle with it a bt and its working - your a real life saver, thanks!!!!

Say i have a database and a excel template front end and i wanted to bring back data to excel but accoring to a start and end date that i would specify in Excel... that possible???

[Charlize]

Hi Charlize

Managed to fiddle with it a bt and its working - your a real life saver, thanks!!!!

Say i have a database and a excel template front end and i wanted to bring back data to excel but accoring to a start and end date that i would specify in Excel... that possible???

Yes, use this at the openrecordset thing [vba]Db.OpenRecordset("SELECT * FROM Store WHERE Mydate >= '01/11/2007', Mydate <= '30/11/2007'")[/vba]Maybe someone else is interested in your coding for the update part ...

[White_Nova]

True that would work but i need to specify the dates in Excel and have Acce pull the data back, the specified dates will change o i cant hard code them...

Below please find my code for updating database in Access from Excel

[VBA]Sub ADOFromExcelToAccess()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=[location of database];"
Set rs = New ADODB.Recordset
rs.Open "[Name of Table in Access]", cn, adOpenKeyset, adLockOptimistic, adCmdTable
r = 5 'Starts at row specified
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Field1") = Range("A" & r).Value
.Fields("Field2") = Range("B" & r).Value

' "Field1,Field2 - need to be replaced with th coloumb headings
' add more fields if necessary
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

Sheets("[Main Sheet Name]").Select
Range("A5").Select
MsgBox ("Data Transfered")



End Sub[/VBA]

[Charlize]

This is my solution for the updating[VBA]Sub Update_Data()
'This coding will update the active row
'You could set this in a worksheet change event
'to update your record when a change in the
'targetcell has been made.
Dim oConn As Object
Dim oRS As Object
Dim sSQL As String
Dim vId As String, vManager As String
Dim vDate As String, vSession As String

Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ActiveWorkbook.Path & "\DataStore.mdb"

vId = Range("A" & ActiveCell.Row)
vManager = Range("B" & ActiveCell.Row)
vDate = Range("C" & ActiveCell.Row)
vSession = Range("D" & ActiveCell.Row)
'MyId is a unique identifier that you can't (may not) change
'Store is your database
'MyManager, MyDate and MySession are your fieldnames
'We update the record by using the unique MyId field
sSQL = "UPDATE Store Set MyManager = '" & vManager & _
"', MyDate = '" & vDate & _
"', MySession = '" & vSession & "' WHERE MyId = '" & vId & "'"
oConn.Execute sSQL
oConn.Close
Set oConn = Nothing
End Sub[/VBA]Use variables instead of hard coded dates

[White_Nova]

Missunderstanding????

In Excel cell(A1) i manually insert a start date, in Cell(A2) i manually insert a end date.

What i need is access to give me the data between these two dates???
Or to have a query in Access understand the two dates inserted by the user into Excel cell(A1,A2) and give me the queried data in Excel...

Is this possible and if so how?

[Charlize]

Again, another piece of coding to play with. Problem with this one is that someone can fill some parts in or not and based on that we need to build the sql query to get to the recordset we want. Anyway, have fun with this one (I think you've got to give something more than some flowers and a box of chocolate ...)[VBA]Sub GetData_From_Certain_Date()
'You have to set a reference to
'microsoft dao 3.6 object library
'Tested under excel 2003
Dim Db As Database
Dim Rs As Recordset
Dim Ws As Object
Dim i As Long
Dim MyPath As String
Dim vStartDate As String
Dim sSql As String
'This is destination sheet if matching records found
Set Ws = Worksheets(3)
'Set the Path to the database. This line is useful because
'if your database is in another location, you just need to change
'it here and the Path Variable will be used throughout the code
MyPath = ActiveWorkbook.Path & "\DataStore.mdb"
'This set of code will activate Sheet3 and clear any existing data
'After clearing the data it will select cell A1
Ws.Activate
Range("A1").Activate
Selection.CurrentRegion.Select
Selection.ClearContents
Range("A1").Select
'Set the Database, and RecordSet This Table exists in the database
Set Db = Workspaces(0).OpenDatabase(MyPath, ReadOnly:=True)
'Since we use the dd/mm/yyyy format, I need to rebuild the date
'string to filter on the acces database
If Worksheets(1).Range("F2").Text = vbNullString Then
vStartDate = ""
Else
'Dates have to be in between # marks
vStartDate = "WHERE MyDate = #" & _
Format(Worksheets(1).Range("F2").Text, "yyyy/mm/dd") _
& "#"
End If
sSql = "SELECT * FROM Store " & vStartDate
Set Rs = Db.OpenRecordset(sSql)
'This loop will collect the field names and place them in the first
'row starting at "A1"
For i = 0 To Rs.Fields.Count - 1
Ws.Cells(1, i + 1).Value = Rs.Fields(i).Name
Next i
'The next line simply formats the headers to bold font
Ws.Range(Ws.Cells(1, 1), Ws.Cells(1, Rs.Fields.Count)).Font.Bold = True
'The next line will get the data from the recordset and copy it
'into the Worksheet (Sheet1).
Ws.Range("A2").CopyFromRecordset Rs
'This next code set will just select the data region and
'auto-fit the columns
Ws.Range("A1").Select
Selection.CurrentRegion.Select
Selection.Columns.AutoFit
Ws.Range("A1").Select
Rs.Close
Db.Close
End Sub[/VBA]

[White_Nova]

after all your help you just name what you want and ill see what i can do ;-)

This works 100% and is exactly what i needed thank you so so so so much for your help... we will be chatting again, will be having a few more project similar to this one and will need your beautiful brain to assist me...

Thanks again

[Charlize]

after all your help you just name what you want and ill see what i can do ;-)

You already said several times thank you. Alltough I was tempted, I don't ask you a thing. My only hope is that you have learned something from my postings (at least that my postings were understandable for you).

This works 100% and is exactly what i needed thank you so so so so much for your help... we will be chatting again, will be having a few more project similar to this one and will need your beautiful brain to assist me...

If I could, I would ... If I can't, I'll try ...

Don't be mistaken about my brain, it's just as good as yours ...