Advice on database in the form of text document

Susan Soo

New Member
Joined
Nov 13, 2004
Messages
33
Hello, :oops:

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
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Try recording a macro of the process then come back with any probs.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,971
Messages
5,599,110
Members
414,289
Latest member
sonintebil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top