Creating a Text File from Microsoft Access Data

You can create a comma- or tab-delimited text file from Access data by using a VBA procedure in Excel. Text files are particularly useful for transferring large amounts of data to a spreadsheet. The example procedure below illustrates how you can create a tab-delimited text file from an ADO recordset. For this procedure to work correctly, you must establish a reference to the Microsoft ActiveX Data Objects 2.6 Library. Refer to Chapter 8 for details on working with text files. After running this procedure, open C:\ProductsOver50.txt in Excel.

Sub CreateTextFile() Dim strPath As String Dim conn As New ADODB.Connection Dim rst As ADODB.Recordset Dim strData As String Dim strHeader As String Dim strSQL As String strPath = "C:\Program Files\Microsoft Office\" _

& "Office\Samples\Northwind.mdb" conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _

& "Data Source=" & strPath & ";" conn.CursorLocation = adUseClient strSQL = "SELECT * FROM Products WHERE UnitPrice > 50" Set rst = conn.Execute(CommandText:=strSQL, Options:=adCmdText) 'save the recordset as a tab-delimited file strData = rst.GetString(StringFormat:=adClipString, _ ColumnDelimeter:=vbTab, _ RowDelimeter:=vbCr, _ nullExpr:=vbNullString) Open "C:\Products0ver50.txt" For Output As #1 For Each f In rst.Fields strHeader = strHeader + f.Name & vbTab Next

Print #1, strHeader Print #1, strData Close #1 End Sub

In Chapter 8, you learned how to work with text files using the FileSystemObject. The procedure below demonstrates how to use this object to create a text file named ProductsOver100.txt:

Sub CreateTextFile2()

Dim strPath As String

Dim conn As New ADODB.Connection

Dim rst As ADODB.Recordset

Dim strData As String

Dim strHeader As String

Dim strSQL As String

Dim fso As Object

Dim myFile As Object

Set fso = CreateObject("Scripting.FileSystemObject") Set myFile = fso.CreateTextFile("C:\ProductsOver100.txt", True) strPath = "C:\Program Files\Microsoft Office\" _ & "Office\Samples\Northwind.mdb"

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _

& "Data Source=" & strPath & ";" conn.CursorLocation = adUseClient strSQL = "SELECT * FROM Products WHERE UnitPrice > 100"

Set rst = conn.Execute(CommandText:=strSQL, Options:=adCmdText)

'save the recordset as a tab-delimited file strData = rst.GetString(StringFormat:=adClipString, _ ColumnDelimeter:=vbTab, _ RowDelimeter:=vbCr, _ nullExpr:=vbNullString) For Each f In rst.Fields strHeader = strHeader + f.Name & vbTab

Next

With myFile

.WriteLine strHeader .WriteLine strData

.Close End With

End Sub

Text Import Wizard - Step 2 of 3

This screen lets you set the delimiters your data contains. You can see how you text is affected in the preview betow.

Delimiters— W lab V Semcolon I"" ¿omrna F Space I- Other: |

r Treat consecutive delimiters as ore Text auaîfier: I

Delimiters— W lab V Semcolon I"" ¿omrna F Space I- Other: |

r Treat consecutive delimiters as ore Text auaîfier: I

-Dataerevlew

Product!!"

Pro duet îlanc-

SupplierID

'at ego ry ID

ïuantityP

19

Thüringer Rostbratwurst

IZ

;

SO bags x

38

löte de B1aye

18

L

L2 - 7S c

"1

<1

1

Because text files can be easily opened in Excel, you can use them for transferring data between Microsoft Access and Microsoft Excel.

0 0

Post a comment