how to update a specific column in excel using access database?
I am trying to compare one column of an excel sheet with the database table.If the value in the column matches with the data in the table,the excel has to be populated with another value in the table in a new column.If match is not found the value in the new column should be kept as "X".
Dim i As Integer
i = 1
datafile = "database.accdb"
With cn
.Provider = "microsoft.ACE.OLEDB.12.0"
.ConnectionString = datafile
End With
cn.Open
Dim oXL As Object
Set oXL = CreateObject("Excel.Application")
Dim oBook As Object
Set oBook = oXL.Workbooks
Dim oSheet As Object
'Set oSheet = oXL.Worksheets(1)
Dim vValue As Variant
Set oBook = oXL.Workbooks.Open("C:\s.xlsx")
Set oSheet = oBook.Worksheets("Sheet1")
Do Until (i >= 5)
vValue = oSheet.Cells(i, 5).Value
MsgBox vValue
Set rs = cn.Execute("select * from table where ID = vValue ")
If (rs.EOF = False) Then
oSheet.Cells(i, 5).Value = rs!field
Else
oSheet.Cells(i, 5).Value = "X"
End If
Re: how to update a specific column in excel using access database?
At a quick glance, I assume ID is a number field. You must make sure that vValue is a number(integer?) and not a string pulled from the excel file. (EDIT)...I see you have dimmed it as a Variant...why not an integer as I really assume your ID in the access table is a whole number.
Re: how to update a specific column in excel using access database?
ID is integer value and vVAlue as variant.I tried giving integer declaration to vvalue.but still the error persists.Msgbox displays inteer value stored in the varaible vValue
Re: how to update a specific column in excel using access database?
Wow...I'm not paying attention...
1-if vValue is an integer (which I am quite sure that is what you want), then your query needs to be like this (with a DIFFERENT table name other than 'table'):
Set rs = cn.Execute("select * from table where ID = " & Cstr(vValue))
Re: how to update a specific column in excel using access database?
A good debugging practice (at least "I" think so) is to place a msgbox before your actual query and see what is being sent to the DB BEFORE you sind it.
In this case, I would have done this:
msgbox("select * from table where ID = " & Cstr(vValue)) and see what appeared. Helps to figure out bad queries (IMHO).
Re: how to update a specific column in excel using access database?
ok...seems we're just beating around the bush....send me your excel file, and your project (including the DB0 IF IT HAS NO SENSITIVE INFORMATION), and I will run it and get right back to you...do you know how to upload here?
Re: how to update a specific column in excel using access database?
Easy....click on the Go Advanced Button.
On that screen which (eventually) comes up, you will see another button called "Manage Attachments" Click on that, and then you will see a popup from which you can use the Basic Uploader and browse you computer. First, zip your project into one file (make sure there is no EXECUTABLE (.exe) in the zip file). Just follow the prompts...pretty easy. In the meantime, I'll see if I can find your error with your code.
Re: how to update a specific column in excel using access database?
No ragh....
this query:
Set rs = cn.Execute("select * from myTitle where ID = " & CStr(vValue))
works fine.
@ ponnu....I created an excel sheet, a db and put your code in a command button (after connecting to by db), and it works fine. Note, in your set rs statement, the name of MY table is 'myTitle'. Your query should be exactly the same except substitute 'emp' (your table name) for 'myTitle'. Other than eliminating your loop for my test, your code is otherwise unchanged in my example, and it works just fine updating my excel sheet with the X or the ID number as desired.
Re: how to update a specific column in excel using access database?
Here ya go....my changes in RED...works just fine......
Code:
Dim cn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim i As Integer
i = 1
datafile = App.Path & "\database4.accdb" 'NOTE...instead of hardcoded path, use app.path instead (the path where your project is located)
With cn
.Provider = "microsoft.ACE.OLEDB.12.0"
.ConnectionString = datafile
End With
cn.Open
Dim oXL As Object
Set oXL = CreateObject("Excel.Application")
Dim oBook As Object
Set oBook = oXL.Workbooks
Dim oSheet As Object
'Set oSheet = oXL.Worksheets(1)
Dim vValue As Integer
Set oBook = oXL.Workbooks.Open(App.Path & "\s1.xlsx")
Set oSheet = oBook.Worksheets("Sheet1")
Do Until (i >= 5)
vValue = oSheet.Cells(i, 5).Value
Set rs = cn.Execute("select * from emp where ID = " & CStr(vValue))
If (rs.EOF = False) Then
oSheet.Cells(i, 5).Value = rs!Status
Else
oSheet.Cells(i, 5).Value = "X"
End If
i = i + 1
Loop
Set oSheet = Nothing 'Make sure this is NOT commented out - otherwise Excel runs in BG
'oBook.Close
Set oBook = Nothing ''Make sure this is NOT commented out - otherwise Excel runs in BG
'oXL.Quit'Set oXL = Nothing
cn.Close
MsgBox "Done"
Make sure you have references as follows:
Microsoft Excel XX.X Object Library
Microsoft ActiveX Data Objects X.X Library
Microsoft ActiveX Date Objects Recordset X.X Library
Last edited by SamOscarBrown; Jun 20th, 2013 at 10:22 AM.
Re: how to update a specific column in excel using access database?
Thank you Sam .Your code works well,but i have one question ,If the loop runs for 10k times its showing error"overflow".What do i need to change.Thanks for helping
Re: how to update a specific column in excel using access database?
I would assume it is because of the amount of data which you can put into an Excel spreadsheet. You might try separating the data into multiple spreadsheets. I have not done this, but I have seen examples recently on how to do it....just google vb6 excel multiple worksheets, and you should see how. Basically, after your first worksheet is maximized (put in all the rows you can), stop your loop, and start it again writing to the new worksheet.
Re: how to update a specific column in excel using access database?
That is a possible issue, but I think only at 64k rows (if using Excel 2003 or earlier, else much bigger)... the current limit is 32k rows due to this: