Automate export query as CSV file using VBS

[WillyG]

I have been exporting members' names, addresses, phone numbers and email addresses to a CSV file to be used with PHP to display the roster on the organization's web site. Using the export wizard I specify there are to be no quotes around strings and the pipe character ("|") is to be the delimiter.

Is there a way to automate this process using VBScript so the next secretary of this organization can do it by clicking a command button on a form listing reports?

Thanks to all -

Bill Hayes

 

[PHV]

Take a look at the DoCmd.TransferText method.

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

 

[mp9]

Or, to help the secretary further, why not write a macro for the export and the use the Handy Access Launcher (freeware from

http://www22.brinkster.com/accessory/resources/001.shtml)

to enable the secretary to schedule that macro to be run at certain times (daily/weekly/monthly and ad-hoc running is supported).

 

[WillyG]

Thanks fellas -
I had created the members.csv file several times in the past and had a specification file in the MSysIMEXSpecs table. I have created a form with no header, footer or detail, but possessing an On Load event. Now my menu of "reports" can include a button that invokes the following:

Option Compare Database

Private Sub Form_Load()
  On Error GoTo Err_Form_Load
  Dim DBStr As String
  path = CurrentDb.Name
     ' This returns the full name and path
     ' of the currently open database.
     ' Need to strip off the database name
     ' to get just the directory with:
  DBStr = path
  Do While InStr(DBStr, "\") > 0
    DBStr = Right(DBStr, Len(DBStr) - __
      InStr(DBStr, "\"))
  Loop
  path = Left(path, Len(path) - Len(DBStr))
     ' Now have the database path ending with "\"
  DoCmd.TransferText acExportDelim, __
    "SpecMembersCSV",__
    "qryMbrWebSiteList", path & "Members.csv"
  MsgBox "Successfully created " & path & __
    "members.csv"
  DoCmd.Close
Exit_Form_Load:
  Exit Sub
Err_Form_Load:
  MsgBox Err.Number & ": " & Err.Description & __
   Chr(13) & Chr(10) & "Error in Form_Load()"
  Resume Exit_Form_Load
End Sub

This seems to work flawlessly. If anyone sees a problem, please let me/us know.

Bill Hayes
Old Dog with a few new tricks