MSQUERY - optimal running

[ajm]

happy new year to all.

I am in a quandary this morning. the organisation i am working for at present has a nasty habit of dumping information on a weekly basis into spreadsheets on the network. The business unit that I work with then adds the new data to the previous weeks and reports on a year to date basis. its ugly and historically labour intensive.

I want to eliminate a few steps along the way for them by using msquery and my rudimentary vba skills. whats got me today is this: is it better to write a macro using "getopenfilename" and insert the new data at the next available row each week, then updating the existing pivot table report, OR, is it possible to refresh the pivot table for only the new weeks data?

can anyone assist?

 

[Fazza]

Without knowing the specifics, difficult to advise.

For sure there will be many possible approaches. Here is another one.

Maintain a list of files from which data has been taken to date. When an update is required, identify the source data files and check them by name against the list. If the file's name is already in the list, do nothing, otherwise take data from that file & add it to the dataset from which reports are run, and of course, add that file's name to the master list of files from which data has been taken. There could be an optional extra step to have user confirmation of the acceptability of the file before taking the data. I guess too you could keep a list of file names to not use - in case there are other files in the search directories.

 

[ajm]

thanks Fazza. extra info hopefully will help qualify my query:

I am creating a YTD report based on weekly data. the weekly data is kept in weekly spreadsheets; "wk 1, Wk 2, Wk 3, etc". Such that at year end (each June), there will be 52 (or 53 leap year) weekly spreadsheets.

Current practice is to take the weekly data, filter it for the content this business unit needs, and add this information it into one consolidated data spreadsheet, and then display the resulting YTD figures in a pivot table.

my first thought was to write a macro to build a parameter query in msquery which would allow the user to select the file with the relevant weeks data to append to the existing consolidated spreadsheet and then they just need to refresh the pivot.

then i thought, maybe I could write a "onrefresh" macro for the pivot that would limit the update range to the new week. it would then use getopenfilename to get the relevant data, etc etc etc. this way, i could eliminate the growing consolidated spreadsheet tab that is currently over 100,000 rows (at half year ).

if i am not mistaken, using the list of files method that you suggest, it adds a level of sophistication to my initial idea by limiting the refresh of sorce data to the file/files in the sorce data folder that do not appear on the master list. this would remove a certain element of user error through potential double ups of any particular week. am i correct?

 

[Fazza]

I don't fully understand, sorry. I suspect though whichever approach you think is best will be great.

[The master list of files was mainly to eliminate the need for user input. No files would need to be (user) selected - such as via getopenfilename - the app would itself identify the file/s to use.]

 

[xenou]

Seems like if you started with a macro to append new data that would be a good start - it would help just to work out the mechanics of extracting the data.

I'm getting the sense you want to automate this to the point where a user doesn't even need to select a file. Possibly - but you need some method for Excel to "know" if there is new weekly data or not. That could be done with some rules for the work flow, or an algorithm based on file names or checking the data itself. I'd be inclined to trust my users on this one - since it only takes a few seconds to do (that is, using your macro, though it might be more safe, really, to separate that from the report so it's not too easy to pull in new data by mistake - let it be a separate task done first, and hopefully easy to fix if a mistake is made).

Note: I'd also be inclined to store the data in Access but I hear a former manager behind me complaining about "not knowing how it works..."

 

[xenou]

Before I crash into my bed tonight I thought a userform could be something to aim for: it could have a label to show current date range in the report data sources, list of files available, list box to pick one or more files, or to clear dates from the data source.