Merging Excel reports

tombrown

Board Regular
Joined
Jun 2, 2006
Messages
50
I am about to start on a new exercise and would liek a point in the
right direction. I dont want the full solution, but a "starter for
10" would help to get me going. I have sporadic experience with Excel
macros & VB (plus some Access experience), but as it is occasional a
lift to the first rung of the ladder will help:-

We have an old trouble ticket database and get an excel report from
it once a week, comprising (unique) issue number in one column and
then further data in the other columns.

Each week we take this report and add a couple of extra columns
(comments, priority etc) that do not exist in the database. At the
moment we take the data for the additional fields from "last weeks"
report and add it to the data just generated from the database to
create "this weeks" report - and we do this manually. (FWIW we then
review the new report & make further changes to the additional fields
as necessary)

I want to create a script that will merge the data from last weeks &
this weeks report for all issues that exist in both reports, and
also to flag issues that were in last weeks report but not in this
(i.e. closed issues) and vice versa (i.e. new issues)

There must be some examples out there of somehting similar that I can
build on?

thanks
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Hi Tom,

Maybe something like this from a few months' ago? http://www.mrexcel.com/board2/viewtopic.php?t=255871

There was data 'previous' and 'current' and a query table report listed records as either not changed, removed, added or changed in a particular field (Risk). If not a query table, a similar use of SQL can also powerfully achieve similar results. For example, via ADO.

HTH, Fazza
 
Upvote 0
I've been playing with this on a flight (better than th emovie) and it looks like exactly what I need - many thanks. I'll take the concepts & bake them into my problem.

I'd like to understand more about the SQL queries - I can understand the query sring you build in the example (sSQL), but am completely dumbfounded by the connection string "sConn" and the code line that pulls it all together:

Code:
With wks 
    .Name = "Report " & Format$(Now, "h.mm am/pm d mmm yy") 
    With .QueryTables.Add(Connection:=sConn, Destination:=.Range ("A1"), Sql:=sSQL) 
        .Refresh BackgroundQuery:=False 
    End With 
    .Move 
End With

also confuses me a bit. Can you point me to somewhere I can read up on this?
 
Upvote 0
Tom,

The connection string has to be what it is. There isn't much that need be understood about it. If you use the macro recorder while manually creating a query - via menu path data, import external data, new database query - you will see a similar low level string that is the connection string. It will be different for different sources. The one you see is for Excel. It'll be different for MS Access. Or for an Oracle database or SQL Server. I don't worry to much about what the string is or means, but it works!

The quoted code, after naming the new worksheet, adds a query table. VBA help on query tables is one way to read about it. I haven't got any specific references. Maybe Chip Pearson or ****'s Clicks websites?

As I mentioned earlier, you don't need a query table. ADO can be used instead. The SQL will be the same for both.

HTH, Fazza
 
Upvote 0
Fazza - thanks for all the info.

I am struggling with one minor item at the moment - the "SELECT" lines in the sSQL string in my version are getting too long, if I just break them over two lines with the usual VBA "_" character then the query itself is syntactically incorrect.

Is there a continuaiton character within SQL I shoudl use in addition to the VBA "_" one?

For example, consider:
Code:
"SELECT 'Unchanged' as [Issue Status], tbl_2Q.D1, tbl_2Q.D2, tbl_2Q.D3 , tbl_2Q.D4 , tbl_1Q.D5", _
"FROM tbl_1Q tbl_1Q, tbl_2Q tbl_2Q", _

I want to break the SELECT line but the following gives me a compile error:-

Code:
"SELECT 'Unchanged' as [Issue Status], tbl_2Q.D1, tbl_2Q.D2, tbl_2Q.D3 ,"_
" tbl_2Q.D4 , tbl_1Q.D5", _
"FROM tbl_1Q tbl_1Q, tbl_2Q tbl_2Q", _
 
Upvote 0
Within the SQL itself, a space is enough. I usually use a carriage return so it is easier to read when I debug.print to the VBE's immediate window. Note, you can't use the underscore character in the middle of a long text string. If you try it like you posted, put an ampersand to concatenate the text together.

The approach I use is either,

for short SQL
Code:
strSQL = join$(array( _
   "SELECT fields", _
   "FROM table", _
   "WHERE whatever"), vbcr)

This uses the JOIN$ function in VBA to create the single text from all the elements of the array. The "vbcr" makes a carriage return the separator between the elements. I use CR, as above, for readibility in the immediate window. Instead you could use " " [space]. Either is good.

The use of JOIN$ also avoids using the text concatenation such as SQL = "SELECT *" & " FROM table" [note the space just at the beginning of " FROM"] which must have spaces somewhere in it, or even SQL = "SELECT *" & CHR(10) & "FROM table". These concatenation approaches I understand are slower.

When there are lots of element to the SQL, I (programmatically) create an array with all the individual elements. This could be with over 100 items if using UNION ALL across dozens of source tables. And then this array of strings, say arSQL, goes neatly into the SQL with
Code:
strSQL=join$(arSQL,vbcr)
This is good - far better than say looping through 100 elements and repeatedly concatenating an ever increasingly long string. I understand such an approach is not efficiently handled in VBA.

HTH, Fazza
 
Upvote 0
Sorry - I may be being a bit thick here, but I still cant see how to split the "SELECT" line. I have the lines SELECT, FROM and WHERE broken by carriage returns as in your example, but when allthe elements on the SELECT line get too many I seem to be forced to keep them all on one line or I get a Compile Error.

I have it working with them all on one line .. but it doesnt make it very easy to read or maintain

I think I need to include the carriage return for code readability but rmoeve it from the SQL string somehow?

(See my example above)
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top