This query would run from an Excel XP module, query an Oracle database and
create a text file.
If you know how to do this, could you please post example code.
Jamie Collins indicated in an earlier post it could be done, but his example
seemed to be for Access. HELP.
The formatting of the delimiters etc for the exported text file can be
achieved by placing a schema.ini file in the export directory.
See JetSql40.chm: Initializing the Text and HTML Data Source Driver
(if you get errors re field and decimal separators try with USenglish
settings first..)
Sub TxtToXls()
Dim txtPath
Dim txtFile
Dim xlsFile
txtPath = "d:\ado test"
txtFile = "myImport.txt"
xlsFile = "d:\ado test\MyExport.xls"
With CreateObject("Scripting.FileSystemObject")
If .FileExists(xlsFile) Then .DeleteFile (xlsFile)
End With
With CreateObject("adodb.connection")
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";" & _
"Data Source=" & xlsFile & ";"
.CursorLocation = 3 'adUseClient
.Open
.Execute " SELECT * " & _
" INTO Import " & _
" FROM [Text;HDR=Yes;Database=" & txtPath & ";].[" & _
txtFile & "];"
.Close
End With
End Sub
Sub XlsToTxt()
Dim txtPath
Dim txtFile
Dim xlsFile
Dim xlsRange
txtPath = "d:\ado test"
txtFile = "myExport.txt"
xlsFile = "d:\MySource.xls"
xlsRange = "Import" 'Sheet1 or Sheet1$a1:d400
With CreateObject("Scripting.FileSystemObject")
If .FileExists(txtPath & "\" & txtFile) Then .DeleteFile (txtPath &
"\" & txtFile)
End With
With CreateObject("adodb.connection")
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Extended Properties=""Text;HDR=Yes;"";" & _
"Data Source=" & txtPath & ";"
.CursorLocation = 3 'adUseClient
.Open
.Execute " SELECT * " & _
" INTO " & Replace(txtFile, ".", "#") & _
" FROM [" & xlsRange & "] IN '' [Excel 8.0;HDR=Yes;Database="
& xlsFile & "]"
.Close
End With
End Sub
Sub XlsToTxt2()
'More ways lead to rome...
Dim txtPath
Dim txtFile
Dim xlsFile
Dim xlsRange
txtPath = "d:\ado test"
txtFile = "myExport.txt"
xlsFile = "d:\ado test\MySource.xls"
xlsRange = "Import" 'Sheet1 or Sheet1$a1:d400
With CreateObject("Scripting.FileSystemObject")
If .FileExists(txtPath & "\" & txtFile) Then .DeleteFile (txtPath &
"\" & txtFile)
End With
With CreateObject("adodb.connection")
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";" & _
"Data Source=" & xlsFile & ";"
.CursorLocation = 3 'adUseClient
.Open
.Execute " SELECT * " & _
" INTO " & Replace(txtFile, ".", "#") & _
" IN '' [Text;HDR=Yes;Database=" & txtPath & ";]" & _
" FROM [" & xlsRange & "]"
.Close
End With
End Sub
keepITcool
< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
> Thanks, I will give this a try...
Quartz,
The examples posted by keepITcool use Excel rather than Oracle as the
data source. A Jet driver/provider must be used for this syntax to
work. If your source database is non-Jet, you will need to be able to
query it using odbc and thereby use Jet's 'pass through' query
functionality.
I don't have Oracle, so here's an example that uses an odbc connection
to a SQL Server database:
SELECT
fname, minit, lname
INTO
[Text;Database=C:\My Folder\;].MyFile#txt
FROM
[ODBC;Driver={SQL
Server};SERVER=MYSERVER;DATABASE=pubs;UID=***;Pwd=***;].employee
;
keepITcool,
I see you used the fso to delete the existing text file. Because in
the Jet context a file is a table, you may use the DROP TABLE syntax
e.g.
DROP TABLE
[Text;Database=C:\My Folder\;].[MyFile#txt]
;
Jamie.
--