Avatar of Cook09
Cook09Flag for United States of America asked on

Create Excel Database - Bolded Rows

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
Microsoft ExcelMicrosoft OfficeMicrosoft Applications

Avatar of undefined
Last Comment
gowflow

8/22/2022 - Mon
gowflow

I like your challenges !!! but as it is almost midnite here will attend tomorrow if no one beats me on it !
gowflow
ASKER
Cook09

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
gowflow

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
gowflow

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
gowflow

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
ASKER
Cook09

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Cook09

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
gowflow

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
ASKER
Cook09

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
gowflow

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
ASKER
Cook09

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
gowflow

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
gowflow

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
ASKER
Cook09

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
ASKER CERTIFIED SOLUTION
gowflow

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Cook09

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
gowflow

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