08-10-10, 13:03 #1Registered User
- Join Date
- Jun 2010
Unanswered: 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.
09-01-10, 18:32 #2Registered User
- Join Date
- Sep 2008
- London, UK
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:
RAD Excel Blog
09-02-10, 02:19 #3Jaded Developer
Provided Answers: 22
- Join Date
- Nov 2004
- out on a limb
do the conversion as a cell format, not at the data level. import the data as a date NOT a text/string value(virtually) all sample code I post will almost certainly contain typos, some logic errors and will need debugging. its air code, providing with good intentions but no guarantees it works or is fit for purpose. in essence yer gets what yer pays for...
PS I'd rather be riding on the Tiger 800 or the Norton