Posts by Walthobum

    Hi all,

    I've been handed a rather unwieldy Access database and an even more unwieldy process for carrying out a task in a new role. There is simply no scope whatsoever for either the database or the process to be changed further up the food chain from me. I basically get what I'm given. That much has been made pretty clear to me. Therefore the only option for me is to simply use what I've got but produce better results by changing the process.

    I need to find a means by which I can export all the records for every unique entry using a query in MS Access. The reason why I'm thinking it should come from a query is because the database table that contains the data I need also contains a ridiculous number of superfluous fields. I can do nothing about this. Therefore I'm hoping to write a query that produces an output, from which I can loop through all of the unique entries for the 'COMMCODE' field and export them to a particular folder as MS Excel files.

    I have searched the internet and tried out some code in MS Access to no avail. Most seem to want to look at recordsets but there are bound to be other ways to carry this out. Is there anywhere I can go to find out more about this? I have some code but it is, at best, incomplete and at worst cobbled together from 2 or 3 different internet solutions to similar queries.

    I have no particular experience in MS Access and feel more at home in MS Excel but I am open to writing and running the coed from whichever application gets the job done quickest and with the least fuss. I have had various run time errors that have crashed the code when I've tested it. I'm at a loss to explain what they mean but I am pretty sure that one of them is because I may not be the only user on the database when I'm running it which makes it difficult to test.

    Any help gratefully received as this is only phase 1 of a massive revision of the processes for which I am getting precious little help from the rest of the organisation.

    Re: Copy filtered range into template workbook

    Somewhat bafflingly it's now bugging on this line:

    ThisWorkbook.SaveAs Filename:=Range("C3").Value & r.Value & ".xlsm", FileFormat:=52

    Prior to moving the clear contents command into the loop this save mechanism was working fine.

    Re: Copy filtered range into template workbook

    Immediate window result is:

    SELECT * FROM [DataRange] Where [REGION_2] = "001_LONDON_NWL_BHH"

    Not sure if that needed to be code wrapped but anyway, that's the output from the immediate window.

    That is the correct field name and the first value in the list.

    Re: Copy filtered range into template workbook

    Currently though this line is only being transacted once in the whole process

    Sheets("Data").Rows("3:"& Rows.Count).ClearContents

    At the start. Nothing is clearing the Data sheet as part of the loop. Therefore the selection simply overwrites the existing data with the new data but, presumably, leaves the old data in situ, as it hasn't been told to clear it.

    If the first report has 1000 lines, it pastes them in no problem.
    If the second report has 1500 lines, it pastes them in no problem.
    If the third report has 500 lines, then the first 500 lines refer to this third report, but the last 1000 lines are the old data from the second report.

    Does that make sense?

    Re: Copy filtered range into template workbook

    I don't see how it could be happening either since there is only one entry in each cell in that column. Therefore in creating a unique list and looping through each line should only appear once in total in all the reports created.

    Is there any way the clearcontents command could leave data 'hanging' from previous reports - i.e. Report 1 is 15000 lines, Report 2 is 10000 lines...

    Could Report 2 somehow have the first 10000 lines form its own report and the last 5000 from Report 1? That is what 'appears' to be happening.

    Re: Copy filtered range into template workbook

    Thanks cytop. I think I'm going to try to work through this myself for a few hours and see where I end up. If I'm in a horrible mess after that then I'd be delighted to receive any advice. I'd like to understand how this works for myself ideally but I do very much appreciate the offer. I can think of a couple of things where I might need some advice straight away.

    Firstly the [company] variable. Obviously I'll be using a different name for my work. however is there a way that I can get an end user to enter that rather than have it hard coded?

    Also the clearcontents line here:


    Is there a way to clear the contents of cells only from rows 3 to the end of the sheet. I have subtotals set up in row 2 which I'm using to populate some charts. It's useful for me to use the subtotals in this way as the sheets are useful to the end user for drill down analysis on various measures.

    Re: Copy filtered range into template workbook

    OK, so as I think we have established previously, I'm a bit of an idiot.

    I had saved my workbook with the data, (and the DATARANGE), in binary format to reduce file size. As soon as I save it as a xlsx the first part works fine.

    Now to try to the second button.

    Re: Copy filtered range into template workbook

    Cheers cytop. It may be that I don't get a chance to test this until tomorrow and may need some further advice with the changes you've mentioned above, but I'll have a real go at it myself first to see if I can understand it without bothering you any further.

    Re: Copy filtered range into template workbook

    Hi cytop,

    thanks for devoting some time to this. In terms of the method you have used it isn't an issue for me at all. I'm not married to the filter/copy technique, it's just something I've managed to get my head around, so I use it a lot. It took me a while. It might take me a while to get this method to sink in but I love to learn something new and I'll give this a go for my full dataset and report back.

    As always, thank you for taking the time to help me out on this. I hope I get to the stage where I can offer someone some help some day.


    Re: Copy filtered range into template workbook

    OK, thanks for looking at it. I'll try to put the process in bullet form below.

    2 workbooks required. WB1 refers to the dataset, WB2 refers to the template. The template has a sheet called data into which I want the filtered range to be copied. There is then a sheet on the template that refers to the sheet called Data and produces series for various charts using sumifs mainly. The charts are on a separate sheet called Charts or Summary. In the samples they are on the same sheet as the calculation.

    In bullets the process is:

    • Create unique list of summary field using a filter in WB1
    • For each value in this list, filter the dataset
    • Open the template, (or switch to the template if it's easier to have it opened), paste the values into the Data sheet on this workbook, WB2
    • Save the sheet as the unique value held as a variable
    • Go back to WB1 and loop through the remaining variables in the list.

    It's the switching between WB1 and WB2 that I am struggling with.

    Re: Copy filtered range into template workbook

    The technique is similar to this. As they are sample workbooks I haven't written any code specifically to do this for these books. Instead I've pasted the code that I have used in the past. I can update but it won't be until later this afternoon/evening. It's just a view of what I have been trying.