Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    3

    Question Unanswered: Tab delimited Files

    To give the whole picture.

    I have an Access2000 db that transfers data to an excel worksheet. This works perfectly. My problem is that I need to now save this Excel worksheet in a Tab Delimited Format.

    It needs to be done this way because of another application that will only accept it in this format.

    Any ideas?

    Thanks.

  2. #2
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    1) why not save it from Acces to a tab-textfile?
    2) save as text... is your solution also from Excel...

    Regards

  3. #3
    Join Date
    Jan 2004
    Posts
    3
    I do not know how to send the data directly to a tab delimited text file. The best I have been able to do is send the data the way I need it to Excel and then I figured I would save the Excel sheet in a Tab Delimited format.

    Below is the code I am using. If there is a way to do the same by sending the data directly to a tab delimited file and bypass having to send it to Excel, I am all ears.


    Private Sub cmd_export_Click()

    Dim xl, xlw, i, dm, scen, str_search, str_eventid, str_filename
    Dim rs As DAO.Recordset

    dm = "DMFILEUSER1-"
    scen = "Block1+"

    str_search = "event_id = " & str_ei
    str_filename = "C:\IERs.csv"

    'this sets xl and xlw to an excel application and the path of the file you want to use

    Set xl = CreateObject("Excel.Application.9")
    Set xlw = xl.Workbooks.Add

    'this makes the excel session visible on your desktop, if this is set to false you will not see the excel screen handy for back ground automation

    xl.Visible = True

    'this sets the record set to the first table
    Set rs = CurrentDb.OpenRecordset("tbl_ier", dbOpenDynaset)

    'this sets which sheet you want to send information to
    xlw.Sheets("Sheet1").Select

    ' this sets I as a value of 2
    i = 2

    'this will put the column headings for the outputs in the excel spreadsheet
    xlw.Application.Cells(1, 1).Value = "#Demand ID"
    xlw.Application.Cells(1, 2).Value = "Producer Name"
    xlw.Application.Cells(1, 3).Value = "Consumer Name"
    xlw.Application.Cells(1, 4).Value = "Type"
    xlw.Application.Cells(1, 5).Value = "Size"
    xlw.Application.Cells(1, 6).Value = "Area"
    xlw.Application.Cells(1, 7).Value = "Method"
    xlw.Application.Cells(1, 8).Value = "Priority"
    xlw.Application.Cells(1, 9).Value = "Classification"
    xlw.Application.Cells(1, 10).Value = "Equip Type"
    xlw.Application.Cells(1, 11).Value = "Perishability"
    xlw.Application.Cells(1, 12).Value = "Producer Device"
    xlw.Application.Cells(1, 13).Value = "Consumer Device"
    xlw.Application.Cells(1, 14).Value = "Start Time"
    xlw.Application.Cells(1, 15).Value = "Stop Time"
    xlw.Application.Cells(1, 16).Value = "Description"


    'this will make the recordset move to the first record before it outputs the rs to excel
    rs.FindFirst str_search

    'start the loop until no records in the rs meet the criteria
    Do Until (rs.NoMatch = True)

    'this sets which cell you will dump information into as I = 2 it will start the second row down first column across and will dump the contents of field 1
    xlw.Application.Cells(i, 1).Value = dm & rs.Fields("ier_id")

    'this sets which cell you will dump information into as I = 2 it will start the second row down second column accross and will dump the contents of field 3
    xlw.Application.Cells(i, 2).Value = scen & rs.Fields("prodname")
    xlw.Application.Cells(i, 3).Value = scen & rs.Fields("conname")
    xlw.Application.Cells(i, 4).Value = rs.Fields("type")
    xlw.Application.Cells(i, 5).Value = rs.Fields("size")
    xlw.Application.Cells(i, 6).Value = rs.Fields("area")
    xlw.Application.Cells(i, 7).Value = rs.Fields("method")
    xlw.Application.Cells(i, 8).Value = rs.Fields("priority")
    xlw.Application.Cells(i, 9).Value = rs.Fields("class")
    xlw.Application.Cells(i, 10).Value = rs.Fields("equip")
    xlw.Application.Cells(i, 11).Value = rs.Fields("perish")
    xlw.Application.Cells(i, 12).Value = rs.Fields("proddev")
    xlw.Application.Cells(i, 13).Value = rs.Fields("condev")
    xlw.Application.Cells(i, 14).Value = rs.Fields("start")
    xlw.Application.Cells(i, 15).Value = rs.Fields("stop")

    'this moves onto the next record meeting the search criteria
    rs.FindNext str_search

    'this sets I onto the next line
    i = i + 1

    'this loops until no more records
    Loop

    'this closes the rs
    rs.Close

    'this frees up the memory block that the record set was residing
    Set rs = Nothing

    End Sub

  4. #4
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    1) select your table
    2) from the menu: File-> Export
    3) Change the default type "Microsoft Access" to "Text File" (or something like that
    4) Click the export button
    5) Choose delimited (radio button) and click advanced button
    6) Change the seperator to {TAB} and change any settings you want to...
    7) Save as.... Name it tbl_ierTabExport
    8) Cancel all Windows....
    9) replace all your code by:
    Code:
    docmd.TransferText acImportDelim,"tbl_ierTabExport","tbl_ier","C:\YourFile.txt",true
    Hint: You might want to put a query inbetween the table and your export to make more userfriendly names and/or add/remove fields or whatever.

    Simple if you know how, isnt it?

    Regards

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •