Hello, 
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
