Merging Excel reports

[tombrown]

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

 

[Fazza]

From previously

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.

All the VBA needs is a text string. You can break it anywhere you want. Just join in like in normal VBA. So,

sSQL = "SELCT field_1, field_2, " & _
   "field_3, field_4, " & _
   "field_5 " & _
   "FROM table"

Be careful with this approach that there is always a space or vbcr or CHR(10) where you need it. (FWIW, as per my previous post, this may not be the best way to do this.)

OK?

 

[tombrown]

Gottit - thanks

Soemtimes needs a few attempts to get into my skull ...

 

[Fazza]

You're welcome. Keep on programmin', Fazza

 

[tombrown]

I'm making good progress with this but I have a problem. Dont think its particular to this specific solution or just a general excel issue, but maybe you might be able to shed some light?

When I have a cell in my report (in particular comments fields) that holds more than 255 characters then, when the data is output to the new sheet, one of two things occurs:

1) The field is truncated at 255 characters
or
2) the contents of the field is dropped altogether

Item 2 seems to happen at random ... :s

Do I just have to live with a 255 character limit or is there a possible workaround here?
Note I see same behaviour (truncation) even if I just copy sheets manually, but not if i copy cells between sheets ...

 

[Fazza]

The 255 character limit is inherent. It will occur with SQL and it will occur with copying.

I seem to never work with long text and am not familiar with the exact details. It is something like you described. I THINK that a workaround is to copy the whole worksheet. I'm sure if you ask the specific question in a new thread someone will quickly provide the right answer. You might even be able to search the archives and find an answer.

cheers, Fazza

 

[tombrown]

The 255 character limit is inherent. It will occur with SQL and it will occur with copying.

I seem to never work with long text and am not familiar with the exact details. It is something like you described. I THINK that a workaround is to copy the whole worksheet. I'm sure if you ask the specific question in a new thread someone will quickly provide the right answer. You might even be able to search the archives and find an answer.

cheers, Fazza

Good plan - I'll post the question. But I have fallen in love with the SQL and am using it to parse all my data, after I have merged it, so I may just end up having to live with the limit

thanks for all your help - i now have a neat solution

 

[Fazza]

Well done.

A little SQL can do a lot of work and make easy things that otherwise far from easy.

 

[tombrown]

By the way I got a suggestion in my 255 limit thread (not sure if you are watching it) to make the connection for the query using ADO - see http://www.mrexcel.com/board2/viewtopic.php?p=1370426#1370426.

I know this is something you mentioned earlier, trouble is I don't know how & a quick search of Excel Help didnt tell me much.

Don't respond on this thread (this is just an FYI), but if you do have any ideas I would be very grateful if you can post them to the thread referenced above

thanks