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