VBA Concatenate problem

[mcinnes01]

Hi,

I have a few different worksheets I have been working on for a while, but I am just adding some new functionality in for emailing that I have been working on. My problem is that before it created the output file by copying the data in to a temp worksheet then copy the temp worksheet to a new workbook.

The way I do this now is by creating a new workbook straight away, avoiding the need to create a temp worksheet.

This is probably me just being daft, but I have an odd field on the output that concatenates a few fields.. Below is an example of my original code.

Please Login or Register  to view this content.

This is the new code I have but I am not sure how to get the concatenated field in?

Please Login or Register  to view this content.

[snb]

Probably this suffices

Please Login or Register  to view this content.

[mcinnes01]

Thanks snb,

The problem is the data on the output sheet is held in a useful structure, meaning it is held in the way it was entered so it can be checked and ammended before the output file is created. This being said I need a way that I can concatenate the field mentioned, as was done previously, but with in the new code structure.

[snb]

So where's your example worksheet ?

[mcinnes01]

Here you go.

[mcinnes01]

http://www.mediafire.com/?264v3vmol3g4l5l

[snb]

if you want to concatenate 2 columns:

Please Login or Register  to view this content.

[royUK]

Not sure if this is what you want

Please Login or Register  to view this content.

[mcinnes01]

What I'm trying to do is get it so row "G" with header "Description" on the destwb to be a concate of row "G" - "H" from the Sourcewb e.g. the Start Date - End Date

So on the Destwb in column G with title DESCRIPTION there should be something like 01/11/2010 - 10/11/2010

[snb]

That's exactly what the code is doing that I suggested in my last post.

[mcinnes01]

How do I get it to copy and paste with your code I'm not entirely sure where to place it, I'm still not 100% with VBA thanks for bearing with me!

[snb]

Please Login or Register  to view this content.

[mcinnes01]

I don't think that will work for me as I need it to fit in with the structure of the output file. The output workbook has to have the specific titles on like in my code or what Roy has provided, as these match the database tables to make the import possible. All I want to do is in row G on the output workbook I want row G - row H from the output sheet on the source workbook. Although copying the entire sheet would be a nice easy solution for this purpose I need to concate the 2 date columns for the databases free format description field. Also on my original code that did this on to a tempary worksheet (as detailed at the very top of this thread) I had to make sure it wouldn't continue inserting " - " all the way down the csv file, as this would make the import fail.

[mcinnes01]

Right we are getting somewhere now... I've used your code snb, and it is working in the sense on concatenating 2 columns, however it is concatenating column A and B in to column A

Please Login or Register  to view this content.

[mcinnes01]

or... with roys cleaner looking version

Please Login or Register  to view this content.

[royUK]

You talk about Row G, presumably that's Column G.

If you use With ....End With then drop the sheet.name code into it

Please Login or Register  to view this content.

[mcinnes01]

Hi the problem is, is that :

Please Login or Register  to view this content.

Is concatenating ***columns A and B including the header in to column A


Below I have tried changing it, It now concates A and B in to A including the header and G and H in to G including the Header?

Any ideas? I'm really not very good with defining ranges in VBA

Please Login or Register  to view this content.

[royUK]

Attach a workbook with what you actually need

[mcinnes01]

here you go...

[mcinnes01]

If you replace:

Please Login or Register  to view this content.

With

Please Login or Register  to view this content.

It is still concating A and B which it shouldn't but it concates G and H correctly. Although it is not diplaying the dates as dates any more? and the headers are being concatenated?

[royUK]

Is this what you are aiming for

Please Login or Register  to view this content.

[mcinnes01]

almost, it is not putting the last rows description in, in column G and it is completely missing column H

[royUK]

It concatenates G & H into I then deletes G & H, leaving the new Column G

[mcinnes01]

basically on the csv file I import, the last 2 columns G & H contain a free format date range description in the format "from date - to date" and then the ID Date aka the from date, respectively.

So:

_________G_____________________H_______
____DESCRIPTION____________ID_DATE____
01/11/2010 - 30/11/2010_______01/11/2010____

[royUK]

So the last result gave the correct text in Column G, but uou want to keep Column H as well?

[mcinnes01]

yes, but it missed off the last rows concation for some reason?

For the row H I was wondering if another line detailing copy column H from sourcewb to column H on destwb would work?

[mcinnes01]

Hi Roy,


This seems to sort out the issue with column H but I can't get my head around why the last row in column G is blank? I don't know if there is a cleaner way of writing what I have patched together?

Please Login or Register  to view this content.

[mcinnes01]

This is the sample CSV file with the code above

[royUK]

Untested, maybe

Please Login or Register  to view this content.

If not I'll look later

[mcinnes01]

I tried the code but it seemed to create a rather strange out put, this is the code and attached is the output:

Please Login or Register  to view this content.

[mcinnes01]

This seems to work I think....

Please Login or Register  to view this content.

The change is the last row + 1

[mcinnes01]

Right I have the other side to the overtime sheet, which is the expenses sheet, they are the same more or less except the layout of the output file is different and the concatenate is in Column B and relates to 3 Columns from the source workbook.

This is the original code that copied it to a new sheet exactly the same as the overtime, but now I want it to copy direct to a new book.

Please Login or Register  to view this content.

[mcinnes01]

This is what I have got so far but I am not sure how to adapt the concated field:

Please Login or Register  to view this content.

[mcinnes01]

I've tried introducing the original code but I cant seem to get it to work, the debugger stops on :

Please Login or Register  to view this content.

This is the code...

Please Login or Register  to view this content.

[royUK]

Have you set the variables Sourcewb & wsdata?

[mcinnes01]

Is that the bit :

Please Login or Register  to view this content.

or is Set Sourcewb...?

[royUK]

What you have posted declares the Variables. You need to give them a value using Set wsData=.

If they are fixed you could declare them as a Constant

[mcinnes01]

I thought I have here??:

Please Login or Register  to view this content.

[royUK]

I think an updated workbook might help.

You don't need those With Statements

Please Login or Register  to view this content.

[mcinnes01]

Here is an example for you, it is more or less the same as overtime except there are many more fields on the output tab for data checking.

Current code:

Please Login or Register  to view this content.

[royUK]

I'm sorry but what are you trying to do now, the lst code was concatenating columns

[mcinnes01]

I am trying to concate columns still, just in a different format.

It concate columns I & ": " & S & " - " & T off the source workbook to column B on the Destination workbook. My problem is there are a lot more columns on the source workbook and the concated column is in column B not G now

[royUK]

I've cleared a lot of error, see what this produces.

[mcinnes01]

Thats it, thank you! It looks like I was just refering to the wrong or overly complicated sheets. Thanks a lot

[royUK]

Did you debug the code?

[mcinnes01]

I didn't get any bugs, I have had to add a few extra fields in so a lot of my named ranges had to be changed, I imagine the that any bugs were probably related to ranges that were defined wrong. My only issue now which I think I remember correctly happended when I first used the CSV code, is that the csv output file lists commas down to row 65536 (when opened in notepad). Its strange though because I have my original which I had fixed this problem on and putting the code side by side it is identical other than the couple on new columns I have added in the exact same code structure

[royUK]

There were several errors that I found, like Sourcewb.wsData, which needed changing to wsData