Importing data into excel every Monday, adding to worksheet since last import

[mejia.j88]

Hi all,



I am sure this has been answered elsewhere, I couldn't find exactly what i am trying to do though.

I have 1 query in access that I want to import to excel every Monday for numerical analysis.


Goal: I want the code to be able to add the newest access data to the same excel worksheet.

In other words, if the last record was row 17, the new import should start with row 18.

I have not really worked with record sets in vba, so i just need some help getting started.


If someone knows of a similar thread, i would be happy to look at that as well.


Thank you so much,
Jorge

[rpeare]

Why not just export the entire dataset to the previous file name? If you have formulas on your excel spreadsheet you should be able to preserve those by exporting to a range within the excel spreadsheet or having your data on one tab and your calculations on a second tab.

Also, what type of 'analysis' are you doing? as long as you're not doing advanced statistical modeling you should be able to do it wholly within access which might save you some headaches as well.

[mejia.j88]

a lot of the analysis is data interpolation, depending on the type of data sets im collecting, the analysis can deal with higher degree polynomials.
most of the data has to do with electrical testing (diode signaling processes) which require trigonometric interpolation.
these functions created through interpolation are also graphed, i dont think graphing is supported by access.

[mejia.j88]

I am also not sure what you mean by exporting to the previous file name.

[June7]

Graphing is supported by Access, although not as well as Excel. I have many graphs in my Access dbs.

Access/VBA has trig functions but I don't know what you mean by trigonometric interpolation.

Isn't a polynomial just an algebraic expression?

[mejia.j88]

trig interpolation is basically just a sum of ratios starting from one point to point n; using trig functions to fit the data.
yes, given an operation and a set of polynomials, one could form a polynomial ring, hence an algebraic structure.

regardless, i'd still like to export it to excel. a lot of people at the company would be seeing the data and would probably feel more comfortable viewing in excel than in access.

[June7]

I think rpeare was suggesting you just overwrite the existing Excel file.

As for performing a process by a set schedule, review https://www.accessforums.net/access/...ule-34151.html

[rpeare]

Yes, I was suggesting you export your entire dataset every time rather than just trying to export 'new' records where your 'old' records left off (which is what it sounded like you wanted to do).

For instance if you have this data set this week:


AAA
BBB
CCC

you want to export that to excel where cell a1 = aaa, a2 = bbb, a3 = ccc.

Next week you've added 2 new records

AAA
BBB
CCC
DDD
EEE

instead of just exporting DDD and EEE when you'd have to cycle through your excel spreadsheet to find the end of your records you could just re-export the entire data set. It's possible to export data to a range of cells/worksheets as well so if you had a spreadsheet with two sheets 'Data' and 'Calculations' you could export all of your access side information to your data tab and never have to touch your calculations tab other than perhaps to add new rows of calculations.

I would suggest though, as June said, that keeping this in access would probably be a better idea. Without knowing exactly what formulas or mathematical operations you're using I can't say for sure but I've never been able to do something in Excel that I couldn't do in Access. Particularly if you're talking about using it as data review rather than data entry tool.