Create a Dynamic Database

[simonplus]

Hi there,

I have created a reconciliation system for the company I work for.

This system summaries the key figures of the business on a weekly basis. At the end of each week, I have to replace the workbook with another for the new week.

Each workbook is named as follows: Reconciliation Week Ending "DATE"

As data are collected, I copy and paste them in my database: an excel sheet.

Is there a way I can create a database that would automatically import data from the reconciliation workbooks as data are entered? Remember that a new workbook is created at the end of each week; thus the database would need to recognize and import data dynamically.

Would you guys recommend to use Access?

Hopefully this email didn't take much of your time.

Thanks

[dflak]

Let me see if I understand the process. You have a workbook called Reconciliation Week Ending YYYYMMDD.xlsx. In this workbook you make manual entries. As you make entries, you manually copy and paste them into a database workbook. The purpose of this database workbook, I presume, is to collect the histories of all the entries.

The short answer is yes, MS-Access would be a good solution. I suggest using MS-Query which is a part of Excel. You can set up a query to get the columns you want and then prompt for a parameter such as week ending date at runtime to select the rows. This prompt can even be read from a cell on the worksheet.

You would be doing things "backwards" from your current process. Instead of making entries on the weekly sheets and filling in the database, you would fill in the Access database and generate the weekly sheet as a report.

You can generate The Reconciliation Week Ending YYYYMMDD files by filling in a start date and end date. Make a copy of this file rename it with the proper datestamp and fill in a new start date and end date and you have next week's sheet*. These workbooks would be week to date each time you refresh the query.

* This part could even be automated. You could have a master workbook that makes a copy of a "template" workbook and renames it and fills in the dates for the queries. All you would have to do is fill in a week ending date, and click a button that says "Make Workbook."

The database itself is the history of all the entries.

[simonplus]

Thanks for your reply.

Just to make sure my communication is effective:

At the end of the week, I replace the workbook with a new one; that is, I move the workbook from a shared network to a folder I have on my desktop. Only then, I copy and paste the content on my Excel database.

It would be much easier, if I could link a database to this folder --- where I store all of these workbooks --- and if the database could automatically --- and I guess dynamically --- import data from the workbooks.

I could send you the excel file if you d like.

Thanks

[dflak]

Let me ask the question another way: what is the purpose of the weekly workbook? Is it merely a place where you record entries, or do other people also record entries in it? Or do other people merely look at it to see how things are going?

[simonplus]

Managers record entries and, as they do so, the workbook generates reports for other people to gauge performance.

[dflak]

OK, unless the managers are willing to change and use Access, then my initial proposal won't fly. Corporate inertia often defeats optimal solutions .

I can envision a system where you collect all your weekly workbooks in one folder. Then on the master sheet, it will look at all these workbooks and process any that have not already been processed. So as a workbook is processed, it is added to a list kept in the master workbook. This set up would assure you won't miss a workbook or double enter one.

The master workbook will open the unprocessed weekly workbook and append the contents to the existing data.

It would take a bit of coding, but we can get Excel to act like a database.

I would need a sample workbook to see if there are any "gotchas."

[simonplus]

I haven't told you that in each workbook there are seven sheets --- days of the week --- plus 5 other sheets for reporting purposes. Also, if you right click on the last sheet and unhide, you will see a sheet whose purpose is to facilitate sales data importing from the database I am trying to create.

Find workbook in the attachment.

[simonplus]

Password is 0214..sorryyy

[dflak]

And a final 2 questions (at least for now).

You want just the Mon-Sun tabs archived. The reports section stays with the sheet but does not get archived.

What does the data file look like? In other words what do you want archived and in what format (Column headers, etc.)?

[simonplus]

I would like to archive the reports as you see them in the Reports tab, the Net Sales figures which are in the hidden tab, and the Total Revenue data in the SalesAnalysis tab.

I hope I am not asking for too much.

[dflak]

Well, the good thing is you have everything "hard coded" as to its position on the pages.

You will get a history file that looks like the attached. Is this what you want?

[simonplus]

that is exactly the way I want it..

[simonplus]

So now the problem is to enable that database/archive to import data from the workbooks as it will be time-consuming to link all the workbooks that I will be using for the weekly reconciliations. This goes back my first post: can you create a database that can automatically import data as new reconciliation workbooks are populated?

Thanks.

SZ

[dflak]

When you move them to the archive directory and click the button, the data will be appended.

What is the naming convention for the weekly file? I have been assuming Reconciliation Week Ending YYYYMMDD.xlsm.

[simonplus]

F&J Reconciliation WE MM-DD-YY

[dflak]

I've run into an impasse. I was depending on using LR2 = sh2.Range(Map(m, 3)).End(xlUp).Row to look up from the last row in a section to the last non-blank line. However, you have the sections defined as named ranges. This means that instead of going to the last non-blank line, the code goes to the top of the range regardless of how much data is in there.

Likewise, since there is a formula in every cell, CountA counts all the rows.

I'll have to figure another way out.

[simonplus]

I see.

Maybe I can create a parallel table to the two that are already there (in the Reports tab) that copies the values from the second table --- the table with blank spaces among values.

You can then use the formula you wrote in your last post on that table.

Am I making sense?

Thanks

[dflak]

Hang in there. There's got to be an easier solution.

By the way I found that the Mon tab had a training space. I corrected that in my template and in the code that comes with the template.

[dflak]

I think I have it working now. If you want to feed the old reports in I will restore the trailing blank on the Mon tab since they probably all have it. Let me know if you want me to do this.

Once I get your answer, I will turn it over to you for final QA.

Warning, this is extremely fragile code. The file names, tab names, ranges, etc. are hard coded. Any change in any of these items will likely break the code.

[simonplus]

This is a new version of the old system so don't even worry about it.

I am ready and excited to see it.

Thanks a lot

[dflak]

In that case, here it is. I've done some QA but not nearly enough. I suggest you make some fictitious weekly files and process them. Then you can clear out the history (keeping the headers) and do it for real.

I'll explain a bit about the file.

There is the Get History button. This does several things: It looks in the archive directory (which is assumed to be the same directory as the Manage File spreadsheet is in, and it checks the names of all the files there. If there are any not on the processed file list, it add them to the list and processes them. This should keep you from missing a file or double processing a file. Keep the first entry (the template file) in this table. It will keep it from being processed. Clear out the other file names used for testing.

The Parameters tab can be hidden if you wish. It contains a table which is like a map telling the code where to find things and where to put them.

Other than that, sit back and enjoy the show. It takes about 30-40 seconds to run.

There is also a Make WE File button. Enter a week ending date in Cell B9. There is some checking to make sure that this is a valid Sunday date, and that the files doesn't already exist. What this does is copies the template and renames it to the name in the Output File Name field. It also opens the file and put's the Week Ending date on the Mon sheet. This will make sure you have a consistent format for the files.

It will create the file in the same directory with the application. If you want to change it to create the file in the shared directory, I've indicated the one line of code you have to change.

BTW: This file isn't very secure. What I suggest is that you get a list of login names and put them in a table on a very hidden sheet. Then when a person opens the spreadsheet, the program can get the windows login and if the person is on the list, the hidden pages (these should be xlveryhidden) are shown. Put a password on the VB project so the casual user can't unhide the pages or add his name to the list.

[simonplus]

I am impressed by it.

When I open the F&J Rec, it gives me an error and consequently it prevents me from seeing the reports.

Also, I am new to Excel VBA and I would love to get some education on how to create databases and the coding behind it. Can you direct me in the right direction?

Thanks.

[dflak]

What kind of an error are you getting? Include the message box that pops up and also launch the browser and note the line that's highlighted in yellow.


Excel isn't much of a database. It works with "flat files" well enough, but after that, it's best to use a real database. Use databases to manage data, use Excel to report and format the data. Both applications work well with each other.

I have several resources but I started with this one: http://www.anthony-vba.kefra.com/index_011.htm. I suggest starting with learning how to reference a workbook, sheet and range. That's very fundamental. Your idea of what the active workbook, sheet or cell is may be different than Excel's idea of what's active. It is almost always best to take nothing for granted and fully reference what you are addressing. A lot of programmers get in trouble because they think the code is doing something in one place, but it's actually looking somewhere else.

The other thing is start every module with the following: Option Explicit

What this does is force you to declare your variables. This is good coding practice, so get into it from the start. Option Explicit helps you find those misspelled variables.

From there, take it a piece at a time. Record some macros to get some syntax. One hint about working with recorded macros: If you see two lines of code like:

ThisThing.Select
Selection.DoSomething

It can often be replaced with ThisThing.DoSomething

Good luck!

P.S. I'm an ex-New Yorker myself: Brooklyn then Rockaway.

[simonplus]

Yeah I am talking about that box that pops up and that line highlighted in yellow.

Will do.

What database would you recommend me to use? Keep in mind that I work for a small restaurant group and budget is very limited.

I live in New York City but I am from Italy. I moved to the US 4 years ago.

[dflak]

For Mom and Pop businesses MS-Access is good enough. I bought a license for Access 2013 for about $100 from Amazon.

If you are going to get working with access, I strongly suggest this group: www.utteraccess.com. There is a lot of good reading material there, and they have people who are willing to help.

The most important thing about a database is setting it up. Read the materials on normalization. A well-designed database saves a lot of grief down the road.

You only need one full copy of the Access database for the developer. Other people who need to work with the database can get free, runtime-only licenses. That means they can do data entry and run reports, but they can't create tables, create new reports, etc. Also there is an application within Excel called MS-Query that can read data from almost any kind of data source including MS-Access. People do not need an Access license to use this feature either.

[dflak]

For Mom and Pop businesses MS-Access is good enough. I bought a license for Access 2013 for about $100 from Amazon.

If you are going to get working with access, I strongly suggest this group: www.utteraccess.com. There is a lot of good reading material there, and they have people who are willing to help.

The most important thing about a database is setting it up. Read the materials on normalization. A well-designed database saves a lot of grief down the road.

You only need one full copy of the Access database for the developer. Other people who need to work with the database can get free, runtime-only licenses. That means they can do data entry and run reports, but they can't create tables, create new reports, etc. Also there is an application within Excel called MS-Query that can read data from almost any kind of data source including MS-Access. People do not need an Access license to use this feature either.

[simonplus]

here it is

[simonplus]

I put some sales data on the first sheet - Monday.

The archive model you sent me would not retrieve the Delivery Sales figure or cell H35.

Thanks.

[dflak]

I can't do anything with the spreadsheet. The enter password dialog box comes up every couple of seconds.

[simonplus]

Try this. It works on my laptop

[dflak]

I noticed that the page "Database Sales" has disappeared. This crashes the code. I mentioned that since Excel is not a database, it has to be programmed to act like a database. I also mentioned that this makes the code very fragile. You cannot change sheet names, not can you change the ranges where data winds up.

Please update the map on the parameters page to match the current configuration of the file.