TITLE:Mutli-value ACCESS Output in Excel
ISSUE:Multi-value Solutions Apr '98
AUTHOR:Nathan Rector
COMPANY:Natec Systems
EMAIL:nater@northcoast.com
HTTP:www.northcoast.com/~nater/
Microsoft Excel is a program well suited for graphing information and easy enough for users to learn the basics in. Recently, I've run into clients that wanted to place the output from a standard ACCESS report into an Excel Spreadsheet. For most companies, they have well established reports already made and don't want to go through the process of converting these reports into ODBC or rewriting them in Visual Basic. There is no need to do this.
With the use of Microsoft VBA (Visual Basic for Applications), all a users need to know is the name of the text file that contains the saved output of the ACCESS statement. There will need to be a one change done to the ACCESS statement that generates these report, but this is miner compaired to what a program would have to do to get similiar results in ODBC.
Included in this article is a program that will extract the report information from the text output text file using Tabs as delimiters. This is the change that a programmer or system administrator will have to do to an existing ACCESS statement; include the Tabs. This simple program will create the Dictionary item for you:
001 *
002 OPEN "DICT","GLT" TO GLT.DICT ELSE STOP 201, "DICT GLT"
003 DICT.ITEM = ""
004 DICT.ITEM<1> = "S"
005 DICT.ITEM<2> = 0
006 DICT.ITEM<3> = CHAR(9)
007 DICT.ITEM<8> = "F;C": CHAR(9)
008 DICT.ITEM<9> = "L"
009 DICT.ITEM<10> = "1"
010 WRITE DICT.ITEM ON GLT.DICT, "TAB"
011 END
Now all someone has to do is include the TAB dictionary items between the normal dictionary items in the ACCESS statement to generate the proper output. Please keep in mind that the TAB dictionary item will have to be included in each file you want to use it. This program does not make it a global dictionary item.
:LIST GLT 1 TAB 2 TAB 5 (I
This ACCESS statement generates the output listed below:
Page 1 GLT 18:38:11 20 Mar 1998
REFERENCE..................... G/L ACCOUNT * AMOUNT TRANS....
DATE
DEDUCTIONS, DIV - 40, CHECKS 216020*-678 07 Jul 97
(315064-315064)
216120*-735
216220*-48
102120*-8152
221520*9613
[405] 1 items listed out of 1 items.
Now comes the challenging part. You will have to get this output saved into a DOS text file. How you do this is up to you. Your tools for doing this vary depending on the operating system you are using. If you are using AP/PRO or another system that has Pic-Lan included, then you can use Pic-Lan to write to a DOS file.
You can also save the output to a multi-value file and then use the EXPORT verb found on most Mutli-value system to transfer it to a disk. See Back-to-Basics article in Multi-value Solutions Issue nnnnnn for more information on the EXPORT verb.
Include the VBA code into your Excel spreadsheet and run the macro. The program will format the excel cells to match the width of each column of the ACCESS report and place it's values into the approperate value into the cell.
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 3/20/98 by Nathan Rector
'
Dim sLine As String
Dim Head As String
Dim Count As Integer
Dim FileName as String
'
FileName = InputBox("Output File:")
' reads the output file
Open FileName For Input As #1
Count = 1
Head = ""
Do While Not EOF(1)
Line Input #1, sLine
' checks to see if this is the heading. If so, then assign the header
' information
If Count = 3 Then
Head = sLine
End If
If Mid(Trim(sLine), 1, 1) = "[" Then
' Number of items.
' [405]1 items listed out of 1
' [401]No Items Present.
ActiveSheet.Cells(Count, 1).Value = sLine
ElseIf Not Head = "" Then
' extract each cell information
ExtractCells sLine, Head, Count
Else
' adds to excel sheet
ActiveSheet.Cells(Count, 1).Value = sLine
End If
Count = Count + 1
Loop
' close openned file
Close #1
End Sub
Sub ExtractCells(Line As String, Head As String, Row As Integer)
Dim I As Integer, LineChar As String, Cell As Integer, CellWidth As Integer
Dim HeadChar As String
Cell = 1
CellWidth = 1
For I = 1 To Len(Head)
HeadChar = Mid(Head, I, 1)
LineChar = Mid(Line, I, 1)
If HeadChar = Chr(9) Then
' gets next cell
ActiveSheet.Cells(Row, Cell).ColumnWidth = CellWidth
Cell = Cell + 1
CellWidth = 1
Else
'Adds the Char to the cell
ActiveSheet.Cells(Row, Cell).Value = ActiveSheet.Cells(Row, Cell).Value & LineChar
CellWidth = CellWidth + 1
End If
Next I
End Sub