Running Excel macro from VB

[Guest]

Is there a way to run an Excel macro from within VB? The macro contains all
the info to call and format a flat text file.

Or, if it is possible to call a flat text file into Access and strip off all
of the headers and footers for each page and save it into an existing table?

 

[Douglas J. Steele]

The following code will open a text file and read it in line by line:

Dim intFile As integer
Dim strBuffer As String
Dim strFile As String

strFile = "full path to text file"
intFile = FreeFile()
Open strFile For Input As #intFile
Do While Not EOF(intFile)
Line Input #intFile, strBuffer
' At this point, you can check whether strBuffer is a header or footer
Loop
Close #intFile

 

[David Lloyd]

One approach to running an Excel macro from Access is to use the Run method
of the Excel Application object. You will need a reference to the
appropriate Excel Object library, of course.

For example:

Dim xlApp as New Excel.Application
Dim wkb as New Excel.Workbook

Set wkb = xlApp.Workbooks.Open("MyWorkbook.xls")

wkb.Application.Run("MyMacro", [Paramater1], [Parameter2], ...)

Access can import text files using the TransferText method. Whether this
would be appropriate in your case depends upon the exact structure of the
text file. You can create an import specification to remove header rows.
What might happen with the footer rows is hard to predict.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Is there a way to run an Excel macro from within VB? The macro contains all
the info to call and format a flat text file.

Or, if it is possible to call a flat text file into Access and strip off all
of the headers and footers for each page and save it into an existing table?