If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > Data Access, Manipulation & Batch Languages > Visual Basic > Automatically Converting Numbers to Month Names upon import using VBA

LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Registered User
Join Date: Jun 2010
Posts: 15
Automatically Converting Numbers to Month Names upon import using VBA


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.


Reply With Quote
  #2 (permalink)  
Registered User
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:
VBA to fire macro upon opening

RAD Excel Blog
Reply With Quote
  #3 (permalink)  
Jaded Developer
Join Date: Nov 2004
Location: out on a limb
Posts: 12,296
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
Is taking part in the 'Distinguished Gentlemans' ride. Being neither distinguished nor a gentleman, but I am keen to raise funds for Prostrate Cancer research. At present I'm threatening to ride a Norton 961 wearing a kilt. If you would like to contribute to Prostrate Cancer research then please consider donating via this page:-
Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On