VBA to Concatenate columns of Access data imported into Excel

[kevin007]

Hi,

Im a newbie with VBA.

I would greatly appreciate some help on this.

I have access tables of sales data by product line with the product lines in Feilds and the sales records listed by month and year. YEAR | MONTH | PRODUCT 1| PRODUCT 2| 3 | 4 | ETC...

I want to import this table into Excel. I create graphs of these figures in Excel. I would like the dates along the bottom of the graphs to read Jan 2000, Feb 2000, Mar 2000,...,...,....,....,Jan 2001, Feb 2001, Mar 2001,...,....,...Jan 2002, Feb 2002, ..., etc,...and so on.

Does anyone have any code that would automatically convert numbers corresponding to months (i.e. 1 through 12 correspond to Jan through Dec) which are imported from an Access DB into Excel into column B. And also is there code that would automatically concontonate the column of years in column A to the column of Months in column B upon import. I don't even know if this is possible...?

I managed to get the import to happen automatically using VBA upon opening the workbook. I would like the charts I make (that are refrencing the cells where the Access data is imported into) to update dynamically upon import which would be strait foreward but its difficult to make a chart with both year AND month feilds. The months that are stored in Access as 1,2,3,4,5 should convert to Jan, Feb, Mar when they are imported using VBA thats tied to the Month column (B) The the year and month data in columns A and B respectively should concantonate into one cell of data data that looks like this - Jan 2000 instead of two cells side by side that look like this 2000 | 1

This should be reletively easy using VBA...?

I hope this explanation of what Im looking for makes sence. I would welcome any suggestions you might have. Or perhaps some code that would make this possible.

Thanks,

Kevin

<!-- / message -->

 

[Slape]

Re: VBA to Concantonate columns of Access data imported into Excel

I think you can do what you want without using vba.

I set up a sheet like what you described and put the "2000" in cell A1 and "1" in cell B1 and the formula below in cell C1.

=VLOOKUP(B1, {1,"Jan";2,"Feb";3,"Mar";4,"Apr";5,"May";6,"Jun";7,"Jul";8,"Aug";9,"Sep";10,"Oct";11,"Nov";12,"Dec"}, 2, FALSE) &" " &A1

I used a VLOOKUP which looks up the numerical value for the month and returns the text corresponding to the month and then the part after that it uses concatenation which excel does automatically if you follow it with the ampersand. You will notice &" " adds a space after the month and the &A1 adds the year.

You can play around with concatenation by entering a "1" into cell A4 and "2" in cell B4 and putting in cell C4 the formula:
=A4 &B4

It should return a 12. Although this wasn't part of your request, I thought I would illustrate it since I used it in my response.

Hopefully this works for you...