Showing header in the Exported Text 2

[khwaja]

I am exporting a query in text format using following command.

Sub ExpPricePoints()

DoCmd.TransferText acExportDelim, "Export Specification", "qPricePoints", "\\nhomadgffs002\Design\04 Revit Server\Price Points.txt", True

End Sub

I need to be able to show following text as the header in this text file so that the exported text is used by other system using the column ref.

",TIPS_ref##other##,ID_Type_Code##other##,Department##other##,
Frame_code##other##,Inserts##other##,Description##other##,
Manufacturer##other##,Height##length##millimeters,
Width##length##millimeters,Depth##length##millimeters"

Any idea how could I achieve this.



Cheers

AK

Note: Using Access 97 for back end A2002 for front end.

 

[VBslammer]

I would just open the file and insert the header after the export:

Function ExportWithHeader() As Boolean
On Error GoTo ErrHandler
  Dim fso As New Scripting.FileSystemObject
  Dim io As Scripting.TextStream
  Dim file As String
  Dim buffer As String
  Dim hdr As String
  
  file = "\\nhomadgffs002\Design\04 Revit Server\Price Points.txt"
  
  hdr = """,TIPS_ref##other##,ID_Type_Code##other##,Department##other##,Frame_code##" & _
        "other##,Inserts##other##,Description##other##,Manufacturer##other##,Height##" & _
        "length##millimeters,Width##length##millimeters,Depth##length##millimeters"""


  DoCmd.TransferText acExportDelim, "Export Specification", "qPricePoints", file, True
  
  Set io = fso.OpenTextFile(file, ForReading)
  buffer = io.ReadAll()
  io.Close
  
  Set io = fso.OpenTextFile(file, ForWriting)
  io.WriteLine hdr
  io.Write buffer
  io.Close
  
  ExportWithHeader = True

ExitHere:
  On Error Resume Next
  Set io = Nothing
  Set fso = Nothing
  Exit Function
ErrHandler:
  Debug.Print Err, Err.Description
  Resume ExitHere
End Function

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[khwaja]

Thank you for your help. I tried to run the function but the following two declarations are causing problem as these are probably user defined. I am getting compile error that user defined type not defined.

Dim fso As New Scripting.FileSystemObject
Dim io As Scripting.TextStream

Could there be something missing?

Cheers

AK

Note: Using Access 97 for back end A2002 for front end.

 

[VBslammer]

You have to add a reference to the "Windows Script Host Object Model" which resides in c:\windows\system32\wshom.ocx

If you want to use a late-bound declaration you can use:
[tt]
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
[/tt]

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[khwaja]

Thanks a lot. Even after adding ref, there was a compile error. But then your alternative solution worked. Much appreciate your help.

Cheers

AK

Note: Using Access 97 for back end A2002 for front end.

 

[PHV]

The correct reference is Microsoft Scripting Runtime

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[khwaja]

Thanks for the tip. If I may ask you or VBSlammer, my output is showing all column headings in addition to the header I wanted to show. Is there some way this can be turned off?



Cheers

AK

Note: Using Access 97 for back end A2002 for front end.

 

[PHV]

DoCmd.TransferText acExportDelim, "Export Specification", "qPricePoints", file, [highlight]False[/highlight]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[khwaja]

Thanks. On a slightly different note, is it possible to use functions as part of the class module (that is triggered by 'on click' event on the form). I am hoping to use the same code to link to a number of command buttons on a form to export several extracts with different file names.

Cheers

AK

Note: Using Access 97 for back end A2002 for front end.

 

[VBslammer]

I hard-coded the filename just for demonstration purposes. Normally I would include parameters to make the function more generic.

Just put the function in a standard module and add parameters for any variable elements:

Function ExportWithHeader(ByVal spec As String, _
                          ByVal qry As String, _
                          ByVal file As String, _
                          ByVal hdr As String) As Boolean
On Error GoTo ErrHandler
  Dim fso As New Scripting.FileSystemObject
  Dim io As Scripting.TextStream
  Dim buffer As String

  DoCmd.TransferText acExportDelim, spec, qry, file, False
  
  Set io = fso.OpenTextFile(file, ForReading)
  buffer = io.ReadAll()
  io.Close
  
  Set io = fso.OpenTextFile(file, ForWriting)
  io.WriteLine hdr
  io.Write buffer
  io.Close
  
  ExportWithHeader = True

ExitHere:
  On Error Resume Next
  Set io = Nothing
  Set fso = Nothing
  Exit Function
ErrHandler:
  Debug.Print Err, Err.Description
  Resume ExitHere
End Function

Then you can include those elements in your function calls:
[tt]
Private Sub cmdExport_Click()
Dim hdr As String

hdr = """,TIPS_ref##other##,ID_Type_Code##other##,Department##other##,Frame_code##" & _
"other##,Inserts##other##,Description##other##,Manufacturer##other##,Height##" & _
"length##millimeters,Width##length##millimeters,Depth##length##millimeters"""

If ExportWithHeader("Spec1", "qryExport1", "F:\test.txt", hdr) = True Then
MsgBox "Export successful"
Else
MsgBox "There was a problem with the export"
End If
End Sub
[/tt]

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[khwaja]

So very grateful for help from both of you.

Cheers

AK

Note: Using Access 97 for back end A2002 for front end.