Solved

Create Excel Database - Bolded Rows

Posted on 2014-01-14
17
258 Views
Last Modified: 2014-01-16
Need to create a database with data from a daily report that has both bolded and non-bolded rows.  The one attachment Error_Report_Database shows how the database is set up and what information is associated within the three worksheets.

The second file is an example of a daily report.  As can be seen, there are both bolded and non-bolded data rows. Additionally, there are two "Posted Late" tabs. One at the beginning and the other at the end of the workbook.  Each of those will be combined into the one "Posted Late" worksheet within the Error_Report_Database.

What is needed:
1. VBA procedure that recognizes only those bolded data rows within each category and appends those rows into the database, Error_Report_Database.
2. The source and database files will be in the same subdirectory.
3. The entire row of data should be copied, no parsing needed.
4. If the database is opened, save and close when the data is copied and appended.
5. The Error Items, in the daily worksheet, don't need to be copied/appended to the database
6. It is not uncommon to have nothing in the first "Posted Late" worksheet, but several rows in the last sheet usually labeled "Posted Late (Date) PM"

The data rows usually start at row 8.  The difficulty is that the Heading Rows are also bolded.

A "LastDataRow" Function is already in place, if that is needed for the code.
Public Function LastDataRow() As Long
 Dim ExcelLastCell As Object, lLastRow As Long
  ActiveSheet.DisplayPageBreaks = False    
  Set ExcelLastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)
    lLastRow = ExcelLastCell.Row
    LastDataRow = lLastRow
End Function 

Open in new window

The same format is also used for finding the LastDataColumn, if needed.
This will be used from a Macro button that calls the procedure within the PERSONAL.xlsb, as the daily report is generated by a third party program.
Error-Report-Database.xlsx
01.14.14-Error-Log-Report.xlsx
0
Comment
Question by:Cook09
  • 10
  • 7
17 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39780609
I like your challenges !!! but as it is almost midnite here will attend tomorrow if no one beats me on it !
gowflow
0
 

Author Comment

by:Cook09
ID: 39780796
gowflow-
I'll wait for your response regardless...You might look at the other one I posted as well.  After listing the steps out 1,2,3 and the Expert still wants more detail, a certain nervousness begins to set in.....if anyone questions, it was per my request.  The concern is that both sheets are set to the same name via the third party app.

Cook
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39781316
The concern is that both sheets are set to the same name via the third party app.
>>> what do you mean ? You have 2 sheets in the same workbook with the same name ? how can this happen ?
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39782203
Let recap my understanding:

We have the file Error_Report_Database that will contain a macro. The purpose of the macro when activated is to open a file (?) the name (?) can we hard code this or user set ? then when the name is chosen the macro will do the following:

Copy all the Bolded rows in that file in their corresponding worksheets after the last existing record. If the worksheet does not exist it will be created. Once all done then the source file is closed and the database file is saved.

Please confirm
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39782473
As you did not reply my last comment then I assumed what I just wrote in it.

Please check this version and let me know if this is what you want. You may in this version select several files at once just shift and select several or just one and check the results.

To make it work activate the macro ImportBoldedRows
gowflow
Error-Report-Database.xlsm
0
 

Author Comment

by:Cook09
ID: 39782711
goflow -
I don't know how one can have two sheets with the same name.  It seems to initially be Web based and then downloaded, with the Option to Open or Save, so it's being cached somewhere I'm sure.

For the second item:

The macro will reside within the Personal.xlsb.  A daily report is downloaded from a third party application.  A macro that also exists within the Personal.xlsb is triggered to reformat the information within this Error Report.xls file.  Part of the formatting is to bold those rows of data that need to copy and pasted into a Word.doc.  These normally pertain to Citys, and the pasted data is done below the City name.
If rows of data are not bolded then they are bypassed as not falling into a "critical type" of situation.

At the moment, each workbook is saved within a single subdirectory, and the filename contains the date in the name field. This is done, so that if an issue arises, it's easy enough to open the document for that particular date.  The issue is what if the date is not known, or what if we want to perform certain metrics on this data.  There is no single respository or database which can be pivoted or manipulated to provide additional information.

This project is in setting up a single data source that will provide the capability to perform  and/or indentify items like Key Performance Indicators.

What came to mind is that since the critical data that needs to be stored is now open, in a workbook, on a daily basis, then prior to closing it out, a macro button can be clicked that would transfer and append this data (bolded rows), into a single database-type file or workbook, parsed by the worksheets or tabs that correspond to the daily report.

As you can see, the worksheets may have several Header Rows, of a particular color, that are also bolded, but don't need to be copied over. If there is a better method of transfering this data, then I'm open to that, this just seems to be a common denominator in determining what needs to be archived and what doesn't.

Whether the Database Workbook actually needs to be opened, I'm not sure.  There was one instance where data could be pulled from a closed workbook, but that was about four years ago when I did that, so certain procedures or processes may have changed.

Most likely, the data will be stored in this single workbook on a yearly basis, and begin anew each January.

Hope this provides the appropriate background on what we would like to achieve through this question.

Cook
0
 

Author Comment

by:Cook09
ID: 39782991
goflow-
Actually what you provided is better than what I had envisioned!!!

I was in a meeting most of the morning, and doing these reports.

Question:  Where would be the best place to format the data being imported, to:
  Cells = No Fill
  Font = Automatic (Black)

In putting together this report I sometimes fill certain cells or color certain words to let me know that I've put them on the report.

Cook
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39783159
Sorry I do not understand what you want. Can you please rephrase your last comment
Cells = No Fill
Font = Automatic (Black)

??? what do you mean by that ???
gowflow
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Cook09
ID: 39783827
gowflow-
Due to manual text coloring or cells being filled in with a color during entry processes, those same colors are copied with the rows, as they are still bold.  Just thought that it might be "a little better," to have those stripped out during the copying / pasting phase into the database.  

This is really not a big issue, as I can manually highlight all of the columns and set the Fill to None and the text Font to "Automatic," or black. The data being brought over works fine and is great.  Just thought I'd see if it was easy enough to do.

Cook
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39784644
ok no big deal this version takes care of the following:

1) Make sure all fonts are black
2) Remove all filtering if any
3) Remove any Subtotal if any
4) Autofit the columns used with the existing data
5) Center the data in the columns.

Please check the version and let me know if all is ok.
gowflow
Error-Report-Database-V01.xlsm
0
 

Author Comment

by:Cook09
ID: 39786093
gowflow -
The overall program works fine.  As a developer, I'm sure you would want to know if certain items that were coded, don't work exactly as intended.  The remove formatting isn't working as the code would seem to indicate.  

Again, this is not a big issue, like the copying one worksheet to another workbook, which is still giving me fits...I was here until 11:30 last night, and it's still not exactly like I want it.  The Expert just doesn't seem to understand, so I'll take the extra hours and do it myself.  It just means working later into the night, on this and my other duties.

But I did run into a couple of interesting scenarios and don't know why they occurred.  The first was after copying the worksheet from Wkb2 to Wkb1, and then closing the Wkb2, the copied worksheet at the end of Wkb1 became all scrambled and unusuable.  Is there still a link that still exists when copying a worksheet versus moving a worksheet?

The second problem is the correct order of saving the workbooks since they are both .xls files to begin with, but the final, Wkb1, will be .xlsx.  Thought I had it figured out from a previous question, but there are still certain situations where the workbook won't reopen, when saved as a .xlsx workbook.

What I'm initially finding, is that if I save Wkb2 as an .xlsx file, prior to copying  the worksheet over, and then saving Wkb1 as an .xlsx file, it seems okay.  But, haven't tested it enough to really say for sure.

Attached is the file that was filled, after selecting several files to copy into the database.  Also, when an autosort is called, what is it sorting by?  Although, I can live with how it is now, but thought you would like to know how the code is actually responding.
Error-Report-Database-V01a.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39786163
I am sorry here !!!!

Are you by any chance mixing 2 issues in the same post ?? are you mixing the 2 questions together. I am lost here.

I will review the formatting now you are correct I will revert shortly with this one.

Meantime for the other question did you try the solution I posted???? I do not have wkb1 and wkb2 !!! this is not my solution. Pls post replies corresponding to questions and do not mix the issues PLEASE !!!
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39786181
ok just found it
please replace this line
WSCopy.Range("A" & ThisMaxRow).Font.ColorIndex = 1

by this line
WSCopy.Range("A" & ThisMaxRow).EntireRow.Font.ColorIndex = 1

it should fix the issue here.
gowflow
0
 

Author Comment

by:Cook09
ID: 39786814
gowflow

Bad News - the fonts were still colored for some of the rows. The file with the change is attached.

Cook
Error-Report-Database-V01a.xlsm
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39786890
Again you should be clear on what you want !!! this is not called Font color it is called background color !! slight difference !!!

Note the font are all black that is what I took care of.

This version will give you for all rows background white and font black the first row that is the title is kept unchanged.

I have manually removed shading for all current rows in the database. You should do the same in your production workbook.. Everything you treat with this macro  from now on will be free of shading and black font.

gowflow
Error-Report-Database-V02.xlsm
0
 

Author Closing Comment

by:Cook09
ID: 39787182
gowflow,
It worked perfectly...sorry for the improper naming conventions.  But, it does work better than I had imagined from the beginning.  Using directory files instead of open workbooks was genius.

Thanks,

Cook
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39787795
Your welcome and glad it did make you happy at the end ! :)
gowflow
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now