Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2010
    Posts
    15

    Automatically Converting Numbers to Month Names upon import using VBA

    Hi,

    Im a newbie with VBA.

    I would greatly appreciate some help on this. 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 concontonate the column of years in column A to the column of Months in column B.

    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,...,....,...Apr 2002, May 2002, ..., etc,...

    I would like these charts to update dynamically upon import. I managed to get the import to happen automatically using VBA upon opening the workbook. Now, the months that are stored in Access as 1,2,3,4,5 I would like to appear as Jan, Feb, Mar etc.

    This should be reletively easy using VBA however, if possible, I would also like to then concantonate the two columns (Year and Date) into a date feild that looks like this: Ex. Jan 2000. So that the dates in the charts are reader freindly.

    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

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    508
    If you import the data using a pivot table then you can have a pivot chart linked to it which will automatically update too. I suggest that you adjust the query so as to import the desired date field rather than performing the manipulation in Excel. Also, you can set the pivot table to automatically refresh on open, which means you do not need any VBA for this task. I think we pretty much covered this on your thread in the Excel board:
    http://www.dbforums.com/microsoft-ex...n-opening.html

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,353
    wrong approach
    do the conversion as a cell format, not at the data level. import the data as a date NOT a text/string value
    http://i792.photobucket.com/albums/y.../miniDGR-0.jpg I've taken part in the Distinguished Gentlemans ride, and although I enjoyed the day the main focus was to raise awareness of Prostrate Cancer and raise some funds for research. Many thanks to the many sponsors who helped me raise some 360 towards this cause, its much appreciated and rather humbling.

    http://www.gentlemansride.com/rider/healdem

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •