S
Susan Soo
Dear Expert,
My company is now using a DOS accounting software package.
I need to repeatedly do the following:-
1) Export data of General Ledger (GL) from the DOS program to my
computer.
2) Click on MS Excel program.
3) Open file in Excel
4) Look in my computer and at the same time change the Files of type to
All Files
5) Click on the text document.
6) Click OK to notify Excel that this file is not in a recognizable
format.
7) Go through the step 1 of the text import wizard and click NEXT
8) Adjust the field widths in step 2 of the text import wizard. I have
to record the numbering of the widths.
9) Click Finish.
10) GL worksheet appeared in Excel. I need to auto fit the column width
to see the numbers and texts in each cell.
11) Use Macro to record each step and come up with a Profit and loss
account (P/L) report presentation instead of GL.
12) I need to repeat the above procedure, if data in the GL in the DOS
program has been updated each time.
I would like to seek you guys’ advice or recommend any reference or web
site to me on the followings:-
1) Any shortcut for (1) to (11) to the above.
2) What should I do?
Option A. Should I continue to use macro to generate a P/L in the GL
worksheet, or
Option B. Should I use macro to create a new worksheet for P/L and use
VBA to copy paste data from GL worksheet to a new sheet.
3) Any advice for Consolidation of P/L?
I need to do a master consolidation of P/L for 18 companies in 1
worksheet named Master. Data in the worksheet Master must link from
another 2 consolidation group of P/L. The two consolidation group of
P/L has 3 companies (worksheet Group A) and 2 companies (worksheet
Group B) respectively. Kindly let me know any VBA to work the above.
Thank you,
Susan
My company is now using a DOS accounting software package.
I need to repeatedly do the following:-
1) Export data of General Ledger (GL) from the DOS program to my
computer.
2) Click on MS Excel program.
3) Open file in Excel
4) Look in my computer and at the same time change the Files of type to
All Files
5) Click on the text document.
6) Click OK to notify Excel that this file is not in a recognizable
format.
7) Go through the step 1 of the text import wizard and click NEXT
8) Adjust the field widths in step 2 of the text import wizard. I have
to record the numbering of the widths.
9) Click Finish.
10) GL worksheet appeared in Excel. I need to auto fit the column width
to see the numbers and texts in each cell.
11) Use Macro to record each step and come up with a Profit and loss
account (P/L) report presentation instead of GL.
12) I need to repeat the above procedure, if data in the GL in the DOS
program has been updated each time.
I would like to seek you guys’ advice or recommend any reference or web
site to me on the followings:-
1) Any shortcut for (1) to (11) to the above.
2) What should I do?
Option A. Should I continue to use macro to generate a P/L in the GL
worksheet, or
Option B. Should I use macro to create a new worksheet for P/L and use
VBA to copy paste data from GL worksheet to a new sheet.
3) Any advice for Consolidation of P/L?
I need to do a master consolidation of P/L for 18 companies in 1
worksheet named Master. Data in the worksheet Master must link from
another 2 consolidation group of P/L. The two consolidation group of
P/L has 3 companies (worksheet Group A) and 2 companies (worksheet
Group B) respectively. Kindly let me know any VBA to work the above.
Thank you,
Susan