VB.NET RSS VB.NET RSS

excel add-in with data from access

Please support our VB.NET advertiser: Intel Parallel Studio Home
Reply
1 2

•
•
Join Date: Dec 2006
Posts: 71
Reputation: pygmalion is an unknown quantity at this point 
Solved Threads: 1
pygmalion's Avatar
pygmalion pygmalion is offline Offline
Junior Poster in Training

excel add-in with data from access

 
0
  #1
Apr 23rd, 2009
Hi

I'm posting this here, because i'm not sure where else and i have a gut feeling i will be doing this with VB.

My goal is the following:
I have an Access database (with four fields, of which the first one is the key) which I'd like to use in Excel.
The table contains about 1,5 million records, and the idea is the user in Excel should enter a number (which should represent the first field in the table), and by the click of a button, excel should pick up the three other fields of the record.
Now, I have searched a lot, and am not sure what's the best way to approach such a challenge.

I'd much appreciate any advice on the matter, as this project is of quite an importance to me.

Thanks very much in advance
pygmalion
Microsoft Employee: "Hey, it compiles! Ship it."
Reply With Quote Quick reply to this message  
•
•
Join Date: Mar 2007
Posts: 306
Reputation: timothybard is an unknown quantity at this point 
Solved Threads: 26
timothybard's Avatar
timothybard timothybard is offline Offline
Posting Whiz

Re: excel add-in with data from access

 
0
  #2
Apr 24th, 2009
Here is a link to a file that shows how to connect to an Access database from Excel: http://www.oecd.org/dataoecd/55/15/35199750.pdf

It is dated and uses an older version of office, but the procedure is the same. While building the query, you are able to select the records based on criteria, such as the key you mention in your post. When you create the query, just select a record to import. Once you have the data imported, turn on the macro recorder in Excel and change the criteria so a different record is selected. Turn off the macro recorder and then you will have the VBA code to select individual records. Modify the VBA to your needs to allow the users to select their own records.
http://www.timothybard.com
http://www.oliveirabard.com
Reply With Quote Quick reply to this message  
•
•
Join Date: Dec 2006
Posts: 71
Reputation: pygmalion is an unknown quantity at this point 
Solved Threads: 1
pygmalion's Avatar
pygmalion pygmalion is offline Offline
Junior Poster in Training

Re: excel add-in with data from access

 
0
  #3
Apr 26th, 2009
Thank you very much for your answer. I did what you said and it worked fine. Now, when I try to tweak the code somewhat to get what I ultimately want, I start getting nasty errors.

This is the code I currently have.

VB.NET Syntax (Toggle Plain Text) 
  1. Sub Macro1()
  2. '
  3. ' Macro1 Macro
  4. ' Macro recorded 24/04/2009 by User
  5. '
  6.     Dim quote As Variant
  7.     quote = Range("A8").Text
  8.  
  9.        With ActiveSheet.QueryTables.Add(Connection:=Array( _
  10.         "ODBC;DSN=MS Access Database;DBQ=C:\db1.mdb;DefaultDir=C:\;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"), Destination:=Range("A9"))
  11.         .CommandText = Array("SELECT `CATERPILLAR-PRICES-2009 Query`.Field1, `CATERPILLAR-PRICES-2009 Query`.Field2, `CATERPILLAR-PRICES-2009 Query`.Field3, `CATERPILLAR-PRICES-2009 Query`.Field4" & Chr(13) & "" & Chr(10) & "FROM `CATERPILLAR-PRICES-2009 Query` `CATERPILLAR-PRICES-2009 Query`" & Chr(13) & "" & Chr(10) & "WHERE (`CATERPILLAR-PRICES-2009 Query`.Field1= & quote &)")
  12.         .Name = "Query from CatDB"
  13.         .FieldNames = True
  14.         .RowNumbers = False
  15.         .FillAdjacentFormulas = False
  16.         .PreserveFormatting = True
  17.         .RefreshOnFileOpen = False
  18.         .BackgroundQuery = True
  19.         .RefreshStyle = xlInsertDeleteCells
  20.         .SavePassword = True
  21.         .SaveData = True
  22.         .AdjustColumnWidth = True
  23.         .RefreshPeriod = 0
  24.         .PreserveColumnInfo = True
  25.         .Refresh BackgroundQuery:=False
  26.     End With
  27.  
  28.     Range("E6").Select
  29. End Sub

When executing this code, I get the following error (which seems to originate from the "ODBC;DSN=MS..."-line).
•
•
•
•
Run-time error '13':
Type mismatch
What i'd like to achieve more is:

1) Arrange the results to come to different cells (not just one next to the other), plus without the column titles.
2) Excel should execute this on more than one entry (entries are listed in same column one below the other).
3) How to execute that macro through a toolbar button [although i'm sure to find this online explained somewhere]

Any help with this is very much appreciated. I've been working on this problem for over a week (tried many things, including straight from Visual Studio in C#, but to no avail), and for the first time I'm starting to see progress (thanks to timothybard!).

Thanks again in advance
pygmalion
Microsoft Employee: "Hey, it compiles! Ship it."
Reply With Quote Quick reply to this message  
•
•
Join Date: Mar 2007
Posts: 306
Reputation: timothybard is an unknown quantity at this point 
Solved Threads: 26
timothybard's Avatar
timothybard timothybard is offline Offline
Posting Whiz

Re: excel add-in with data from access

 
1
  #4
Apr 27th, 2009
As far as your error.. can you show the original code, which I assumed worked, and the modified code so that we can see the changes you made.

On the line you referenced, you have the following:
VB.NET Syntax (Toggle Plain Text) 
  1. FROM `CATERPILLAR-PRICES-2009 Query` `CATERPILLAR-PRICES-2009 Query`

Why is the query mentioned twice?

As far as your other concerns:
1) If this works, I would have the results in a separate sheet and then pull over the information that you want either through vba code, reference or lookup.
2) I would use VBA to loop through each entry and update the single query and then have code to retrieve the required results and place them where you want them
3) Executing macros through toolbars is rather straightforward once you find a resource showing how. Let me know if you are unable to find a resource or need specific help

Tim
http://www.timothybard.com
http://www.oliveirabard.com
Reply With Quote Quick reply to this message  
•
•
Join Date: Dec 2006
Posts: 71
Reputation: pygmalion is an unknown quantity at this point 
Solved Threads: 1
pygmalion's Avatar
pygmalion pygmalion is offline Offline
Junior Poster in Training

Re: excel add-in with data from access

 
0
  #5
Apr 27th, 2009
Thanks for your answer. Here's the original code (created automatically, as you had suggested), which I then intermingled with other code I found in online examples. Maybe I should go back and depart only from this code?

VB.NET Syntax (Toggle Plain Text) 
  1. Sub Macro1()
  2. '
  3. ' Macro1 Macro
  4. ' Macro recorded 27/04/2009 by User
  5. '
  6.  
  7. '
  8.     With Selection.QueryTable
  9.         .Connection = Array(Array( _
  10.         "ODBC;DSN=MS Access Database;DBQ=C:\db1.mdb;DefaultDir=C:\;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
  11.         ))
  12.         .CommandText = Array( _
  13.         "SELECT `CATERPILLAR-PRICES-2009 Query`.Field1, `CATERPILLAR-PRICES-2009 Query`.Field2, `CATERPILLAR-PRICES-2009 Query`.Field3, `CATERPILLAR-PRICES-2009 Query`.Field4" & Chr(13) & "" & Chr(10) & "FROM `CATERPILLAR-PRICES-2009 Que" _
  14.         , _
  15.         "ry` `CATERPILLAR-PRICES-2009 Query`" & Chr(13) & "" & Chr(10) & "WHERE (`CATERPILLAR-PRICES-2009 Query`.Field1='5N8380')" _
  16.         )
  17.         .Refresh BackgroundQuery:=False
  18.     End With
  19. End Sub

About the other subjects:

1) I see. And how do I specify in the VBA code (above) the target sheet for the retrieving of data?
2) This sounds excellent, but you could you please go a little more in details, in how I would practically go about and do that. (My syntaxic knowledge in VBA is quite limited).
3) Thanks. I hope I can find some recource on my own.

Thanks again very much for all your support. Seriously, very much appreciated.

Regards
pygmalion
Microsoft Employee: "Hey, it compiles! Ship it."
Reply With Quote Quick reply to this message  
•
•
Join Date: Mar 2007
Posts: 306
Reputation: timothybard is an unknown quantity at this point 
Solved Threads: 26
timothybard's Avatar
timothybard timothybard is offline Offline
Posting Whiz

Re: excel add-in with data from access

 
0
  #6
Apr 30th, 2009
Just so I'm clear, the code in the previous post does work, but you want to be able to select which number is selected (instead of always just showing number 5N8380). Is that correct?
http://www.timothybard.com
http://www.oliveirabard.com
Reply With Quote Quick reply to this message  
•
•
Join Date: Mar 2007
Posts: 306
Reputation: timothybard is an unknown quantity at this point 
Solved Threads: 26
timothybard's Avatar
timothybard timothybard is offline Offline
Posting Whiz

Re: excel add-in with data from access

 
0
  #7
Apr 30th, 2009
If the code in the previous post did work, then I would try the following code:

VB.NET Syntax (Toggle Plain Text) 
  1.  
  2. Sub Macro1()
  3. '
  4. ' Macro1 Macro
  5. ' Macro recorded 27/04/2009 by User
  6. '
  7.  
  8. '
  9. 	Dim quote As String
  10.     quote = Range("A8").Text
  11.  
  12.     With Selection.QueryTable
  13.         .Connection = Array(Array( _
  14.         "ODBC;DSN=MS Access Database;DBQ=C:\db1.mdb;DefaultDir=C:\;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
  15.         ))
  16.         .CommandText = Array( _
  17.         "SELECT `CATERPILLAR-PRICES-2009 Query`.Field1, `CATERPILLAR-PRICES-2009 Query`.Field2, `CATERPILLAR-PRICES-2009 Query`.Field3, `CATERPILLAR-PRICES-2009 Query`.Field4" & Chr(13) & "" & Chr(10) & "FROM `CATERPILLAR-PRICES-2009 Que" _
  18.         , _
  19.         "ry` `CATERPILLAR-PRICES-2009 Query`" & Chr(13) & "" & Chr(10) & "WHERE (`CATERPILLAR-PRICES-2009 Query`.Field1='" & quote & "')" _
  20.         )
  21.         .Refresh BackgroundQuery:=False
  22.     End With
  23. End Sub

Please note that I added the dim quote as string line, the quote = Range("A8") line and I changed Field1='5N8380')" to .Field1='" & quote & "')"

If the original code worked, then this should allow the user to enter something into cell A8 and then update the data based on that cell after the code is run.
http://www.timothybard.com
http://www.oliveirabard.com
Reply With Quote Quick reply to this message  
•
•
Join Date: Dec 2006
Posts: 71
Reputation: pygmalion is an unknown quantity at this point 
Solved Threads: 1
pygmalion's Avatar
pygmalion pygmalion is offline Offline
Junior Poster in Training

Re: excel add-in with data from access

 
0
  #8
May 5th, 2009
Thanks very much, it works like a charm.

Finally, I'm having difficulty with the option of being able to apply the query to many entries at the same time (which are just entered one under the other). I tried quite a few things (from just extra variables and changing the select-statement to include those, to loops) but it just doesn't seem to be working. I always somehow end up with the error statement:
VB.NET Syntax (Toggle Plain Text) 
  1. Run-time error '1004'

Anyway, hope you can help me out with this last thing.
Thanks again very much for your help.
Microsoft Employee: "Hey, it compiles! Ship it."
Reply With Quote Quick reply to this message  
•
•
Join Date: Mar 2007
Posts: 306
Reputation: timothybard is an unknown quantity at this point 
Solved Threads: 26
timothybard's Avatar
timothybard timothybard is offline Offline
Posting Whiz

Re: excel add-in with data from access

 
0
  #9
May 5th, 2009
Are you trying to allow the user to type in several quote values, all right under each other, and have the query show the data for each of those quote values?

You can try something similar to this:

VB.NET Syntax (Toggle Plain Text) 
  1.     'This code takes a range and puts the values into a common delimited string
  2.     Dim cell As Range
  3.     Dim values As String
  4.  
  5.     For Each cell In Range("A8:A10")
  6.         values = values & "'" & cell & "', "
  7.     Next
  8.  
  9.     values = Left(values, Len(values) - 2)
  10.  
  11.     'This code updates the query with the values
  12. 	With Selection.QueryTable
  13.         .Connection = Array(Array( _
  14.         "ODBC;DSN=MS Access Database;DBQ=C:\db1.mdb;DefaultDir=C:\;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
  15.         ))
  16.         .CommandText = Array( _
  17.         "SELECT `CATERPILLAR-PRICES-2009 Query`.Field1, `CATERPILLAR-PRICES-2009 Query`.Field2, `CATERPILLAR-PRICES-2009 Query`.Field3, `CATERPILLAR-PRICES-2009 Query`.Field4" & Chr(13) & "" & Chr(10) & "FROM `CATERPILLAR-PRICES-2009 Que" _
  18.         , _
  19.         "ry` `CATERPILLAR-PRICES-2009 Query`" & Chr(13) & "" & Chr(10) & "WHERE (`CATERPILLAR-PRICES-2009 Query`.Field1 IN (" & values & "))" _
  20.         )
  21.         .Refresh BackgroundQuery:=False
  22.     End With

The first part of the code will take a range of cells and produce a string that stores all the values in one place. When you run the code, the values variable should store something that looks like this:

'Quote1', 'Quote2', 'Quote3'

Of course, instead of Quote1, it will be the first value the user typed in.

The second part of the code is exactly the same as the code I previously posted with the exception of the WHERE clause. In my original code, I used a where clause of Field1 = 'Quote'... In the code I just posted, the WHERE clause will look like this:
Field1 IN ('Quote1', 'Quote2', 'Quote3')
This will return all the records where Field1 is one of the three values.

I don't know how many values the users will enter, so you may need to change the range. If you use named ranges, then you can simply put the name in the Range function and just update the named ranges to include additional values.

Let me know how it works or if you run into any issues.
http://www.timothybard.com
http://www.oliveirabard.com
Reply With Quote Quick reply to this message  
•
•
Join Date: Dec 2006
Posts: 71
Reputation: pygmalion is an unknown quantity at this point 
Solved Threads: 1
pygmalion's Avatar
pygmalion pygmalion is offline Offline
Junior Poster in Training

Re: excel add-in with data from access

 
0
  #10
May 5th, 2009
Thank you so much for the code, it works amazingly well. The way I arranged it is that user enters everything in a 3rd sheet, while the data is formatted as should be in the other two sheets (referenced twice the same for different languages).

One problem that does still seem to occur, is that I can only give five entries to generate (while I have entered a range of almost 35 entries - which I would like to put till at least 250). Any more than that, and Excel gives a Run-time error ('13' or '1004' - "Type mismatch"). It may be a bit hard to explain, so I uploaded the file to my server. Maybe you can have a look at it, and see what causes the trouble.
Also, if a different cell (than one of the entered entries) is selected while executing the Macro, a same error is given.

Thank you so much
pygmalion

[After some consideration, I decided to send it to you via private message - if anyone else is willing to help, I'd gladly send it to him/her as well]
Microsoft Employee: "Hey, it compiles! Ship it."
Reply With Quote Quick reply to this message  
Reply
1 2

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the VB.NET Forum
Thread Tools Search this Thread



.net .net2008 2005 2008 access account arithmetic array arrays basic bing button buttons c# center check checkbox code combobox convert crystalreport data database datagrid datagridview date dissertation dissertations dropdownlist excel fade file-dialog filter ftp generatetags google gridview hardcopy images inline input insert intel internet listview mobile monitor ms net networking objects output panel passingparameters picturebox picturebox1 port position print printing problem project read remove save searchbox searchvb.net select serial server shutdown soap sorting survey table tcp temperature text textbox timer timespan toolbox trim update user validation vb vb.net vb.netformclosing()eventpictureboxmessagebox vb2008 vbnet view visual visualbasic visualbasic.net visualstudio2008 web winforms wpf year
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC