Record Macro

[wrytat]

We are using Excel 2003. Every month we will import data from a database of
our MS SQL 2000 server to create a report. Basically, this procedure is
repetitive every month and we wonder if we could make use of the "Record
Macro" function to record down the whole procedure such that every month we
will just need to run the macro, without having to repetitive the same steps
over and over again every month.

However, as we are still not familiar of playing around with Macros, we
experience some problems such as, since the data that we import from the
database is according to the month the report is generated, is there any way
to enter the month and year and thereafter, the report will appear? Is there
any recommended links or source of reading that you could recommend?

Thank you.

 

[Bob Phillips]

You can get the month either by querying the user (using the InputBox
method), or by deriving it. For instance, if the month is always the one
previous to this month, use

MonthNum = Month(Date) - 1

or

MonthName = Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mmmm")

 

[wrytat]

Thank you for the help. By the way, I have tried recording and editing the
macro. Everything seems to work, except for the part whereby I format 4
columns to Number instead of Text. I muliply the 4 columns by 1 to convert
them into Number. But this seems to be unable to be recorded as I tried after
running the macro, the 4 columns remain text. What should I do?

 

[Bob Phillips]

Try setting the numberformat property

Columns("A").NumberFormat = "0.00"

or whatever format you need