• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Fetch MS-Acces Data by Excel VBA

nagovind

Member
Dear All


I have a program in which i have generated 65560 numbers to construct a Chart (X-Y scatter) fine


My aim is to reduce the file size of the Excel file


So i will use the MS-Access as the back end to store the data if and then required i will take the data from the MS-Access and place it in the excel fine


I'm using array function to generate the series point. (THANKS to HUI)


If anybody provides a code to write the MS-Excel VBA generated array to MS-Access data base and bring back the same data whenever required.....it will be of great help to me please
 

nagovind

Member
Sample hierarchy will be as below....


from a Excel Excel VBA will be executed


Dim Myarray(1 to 100, 1 to 1) as Variant


For i = 1 to 100


Myarray(i,1) = i


Next i


The above Myarray has to be moved (to and from) to MS-Access data base...WRITE Myarray() to MS access database


it has to create a MS Access file (with a pre-defined file name) if access file already exists it can be over written


access file has to be created in the same directory where the excel file is placed


Read Myarray() from MS access database and paste to MS-Excel


Any help please ?
 

dan_l

Active Member
I'm not real clear as to what you're trying to do:


Moving data to and from an Access database is relatively easy. Depending on what kind of data you're shuffling, I would think that creating an access database, not to mention creating a table on the fly would be 'wonky'.


Can you tell us a bit more about what your'e trying to do?
 

JP

New Member
There's some sample code here that shows you how to run a query against an Access MDB and return the result into an array in Excel:


http://www.ozgrid.com/forum/showthread.php?t=18289&page=1
 

nagovind

Member
Hi dan_I & JP


thanks for your reply


dan_I,


My application is very simple.


I have to plot a graph using X-Y scatter

Total poits of X and Y axis is 65000 points to create 1 curve

Total no of curve is 12 nos

To Switch ON and OFF the cureves i will duplicate those X-Y poits by copying and clearing

While duplicating the size of the XL file increaes ...so to avaod that i'm trying to retain the orginal points in MS ACCESS data base and if required i will import the data from MS ACCESS to Excel


Sample code is

For i = 1 to 65000

Cells (i,1) = i

Next i


For j = 1 to 65000

Cells (j,1) = j

Next j


So in the Coloum A and B i have X-Y poits to plot the X-Y scatter chart. But i need to Switch ON and OFF the curve so i will use VBA to Copy the poits from Coloum A & B to Coloum C & D and the C & D coloum is linked to CHART.


Here i need to store the A & B coloum (original) values in MS ACCESS


Hope the above clarifies my query in detail

Please extend your help
 

dan_l

Active Member
Well,


,

The easiest way to pull data down from an access database is using MS Query. In 07, the data will automatically come down in a table which - if I recall correctly - can be plugged into your chart. IE: book1.xls!table1[xvalues]. If you're using 03, you can still use MS Query, but you'll have to do manually create a dynamic range to achieve the automatic chart.


Either way, you can add calculated fields from the query or you can play with the query table to automatically fill in some formulas, so that might help you add/remove your curve without having to jerk around with VBA.


I guess what I'm not clear on is the reason we're using an array.
 
Top