Exportar a Excel QueryTables.Add Auxilio !

Hola, me pregunto si no es mejor usar un DTS para ello




Maxi

Buenos Aires - Argentina

Desarrollador .NET 3 Estrellas

Mail: Maxi_accotto[arroba]speedy.com.ar

MSN:


"Jose Manuel Davila" escribió en el mensaje
news:

que tal exporto a excel con esta rutina desde vb6


aqui envia la instruccion para que exporte
var_cnn = "ODBC;DRIVER=SQL Server;SERVER=" & parametros(0) &
";UID=sa;PWD=" & parametros(3) & ";DATABASE=" & parametros(1)

With oWorkBook.ActiveSheet.QueryTables.Add(Connection:=var_cnn, _
Destination:=oWorkBook.ActiveSheet.Cells(4, 1), SQL:=var_sql)
' Actualizamos los datos...
.Refresh
End With
si son bastantes datos todavia no acaba de exportar sin embargo el
sistema sigue su ejecucion en la sigueinte fila


oSheet.Range("D5").Select

'OEXCEL.ActiveWindow.FreezePanes = True
Screen.MousePointer = vbDefault
OEXCEL.DisplayAlerts = False
oWorkBook.SaveAs var_Nombre_Archivo
oWorkBook.Close 'SaveChanges:=True, Filename:=var_Nombre_Archivo
OEXCEL.Quit
Set oWorkBook = Nothing
Set oSheet = Nothing
Set OEXCEL = Nothing


si todavia no termina el archivo se guarda vacio

como podre controlar esto


gracias a todo ayuda ofrecida

Within Excel VBA, you can use ADO to open a recordset, then copy the
recordset into your Excel worksheet using the CopyFromRecordset method. See:
Q306125 HOW TO: Import Data from SQL Server into Microsoft Excel
http://support.microsoft.com/defaul...US;Q306125

Here is a list of [English-language] articles about Excel as a data source
or destination:

USING ADO AND ADO.NET WITH EXCEL: Resources and Known Issues
June 2003

General
-
Q326548 HOW TO: Use Jet OLE DB Provider 4.0 to Connect to ISAM Databases
http://support.microsoft.com/defaul...US;Q326548
Q257819 HOWTO: Use ADO with Excel Data from Visual Basic or VBA
http://support.microsoft.com/defaul...US;Q257819
Q303814 HOWTO: Use ADOX with Excel Data from Visual Basic or VBA
http://support.microsoft.com/defaul...US;Q303814
Q278973 SAMPLE: ExcelADO Shows How to Read/Write Data in Excel Workbooks
http://support.microsoft.com/defaul...US;Q278973
Q195951 HOWTO: Query and Update Excel Data Using ADO From ASP
http://support.microsoft.com/defaul...US;Q195951

Transferring Data into Excel
-
Q247412 INFO: Methods for Transferring Data to Excel from Visual Basic
http://support.microsoft.com/defaul...US;Q247412
Q295646 HOWTO: Transfer Data from ADO Data Source to Excel with ADO
http://support.microsoft.com/defaul...US;Q295646
Q246335 HOWTO: Transfer Data from ADO Recordset to Excel with Automation
http://support.microsoft.com/defaul...US;Q246335
Q319951 HOW TO: Transfer Data to Excel by Using SQL Server DTS
http://support.microsoft.com/defaul...US;Q319951
Q306125 HOW TO: Import Data from SQL Server into Microsoft Excel
http://support.microsoft.com/defaul...US;Q306125

Known Issues

Q319998 BUG: Memory Leak When You Query Open Excel Worksheet with ADO
http://support.microsoft.com/defaul...US;Q319998
Q316809 BUG: No ADO Connection Error When Excel Workbook Is Not Found
http://support.microsoft.com/defaul...US;Q316809
Q314763 FIX: ADO Inserts Data into Wrong Columns in Excel
http://support.microsoft.com/defaul...US;Q314763
Q316475 PRB: "Operation Must Use an Updateable Query" Error Message
http://support.microsoft.com/defaul...US;Q316475
Q300948 BUG: Incorrect TABLE_TYPE Is Returned for Excel Worksheets
http://support.microsoft.com/defaul...US;Q300948
Q294410 ACC2002: Nulls Replaced w/ Next Field's Data Exporting to Excel
http://support.microsoft.com/defaul...US;Q294410
Q293828 BUG: Excel File Size Grows When You Edit ADO Recordset
http://support.microsoft.com/defaul...US;Q293828
Q288343 BUG: Excel ODBC Driver Disregards FirstRowHasNames/HDR Setting
http://support.microsoft.com/defaul...US;Q288343
Q246167 PRB: Collating Sequence Error Opening XLS as ADO Recordset
http://support.microsoft.com/defaul...US;Q246167
Q211378 XL2000: 'Could Not Decrypt File' Error with Password-Protected File
http://support.microsoft.com/defaul...US;Q211378
Q194124 PRB: Excel Values Returned as NULL Using DAO OpenRecordset (mixed
data types)
http://support.microsoft.com/defaul...US;Q194124
Q189897 XL97: Data Truncated to 255 Characters with Excel ODBC Driver (Rows
To Scan)
http://support.microsoft.com/defaul...US;Q189897

Known Issues - .Net-specific
-
Q316831 PRB: Cannot Configure Data Connection to Excel Files in VS .NET
http://support.microsoft.com/defaul...US;Q316831
Q316756 PRB: Error w/ ADO.NET OLEDbDataAdapter to Modify Excel Workbook
http://support.microsoft.com/defaul...US;Q316756

VB.Net

Q318373 HOW TO: Use GetOleDbSchemaTable with Excel in Visual Basic .NET
http://support.microsoft.com/defaul...US;Q318373
Q316934 HOW TO: Use ADO.NET to Retrieve and Modify Records in Excel
http://support.microsoft.com/defaul...US;Q316934
Q306022 HOW TO: Transfer Data to Excel Workbook Using Visual Basic .NET
http://support.microsoft.com/defaul...US;Q306022
Q302094 HOWTO: Fill/Retrieve Excel Data Using Arrays From VB .Net
http://support.microsoft.com/defaul...US;Q302094

Web/ASP
-
Q319180 HOWTO: Transform Dataset to Spreadsheet XML for Microsoft Excel
http://support.microsoft.com/defaul...US;Q319180
Q276488 HOWTO: Use ADODB.Stream to Read Binary Files to the Browser
http://support.microsoft.com/defaul...US;Q276488
Q257757 INFO: Considerations for Server-Side Automation of Office
http://support.microsoft.com/defaul...US;Q257757
Q199841 HOWTO: Display ASP Results Using Excel in IE with MIME Types
http://support.microsoft.com/defaul...US;Q199841
Q195951 HOWTO: Query and Update Excel Data Using ADO From ASP
http://support.microsoft.com/defaul...US;Q195951

Web/ASP.Net - .Net-specific

Q317719 HOW TO: Export Data in DataGrid on an ASP. NET WebForm to Excel
http://support.microsoft.com/defaul...US;Q317719
Q311731 HOW TO: Query and Display Excel Data by Using ADO.NET, VB .NET
http://support.microsoft.com/defaul...US;Q311731
Q308247 HOW TO: Use ASP.NET to Query and Display Database Data in Excel
http://support.microsoft.com/defaul...US;Q308247
Q307603 HOW TO: Write Binary Files to the Browser Using ASP.NET & VB
http://support.microsoft.com/defaul...US;Q307603

SQL Server/DTS
Q321686 HOW TO: Import Data into SQL Server from Excel
http://support.microsoft.com/defaul...US;Q321686
Q319951 HOW TO: Transfer Data to Excel by Using SQL Server DTS
http://support.microsoft.com/defaul...US;Q319951
Q306397 HOWTO: Use Excel w/ SQL Linked Servers & Distributed Queries
http://support.microsoft.com/defaul...US;Q306397
Q306125 HOW TO: Import Data from SQL Server into Microsoft Excel
http://support.microsoft.com/defaul...US;Q306125
Q281517 PRB: Transfer of Data from Jet 4.0 OLEDB Source Fails w/ Error
http://support.microsoft.com/defaul...US;Q281517
Q257757 INFO: Considerations for Server-Side Automation of Office
http://support.microsoft.com/defaul...US;Q257757
Q236605 PRB: DTS Wizard May not Detect Excel Column Type for Mixed Data
http://support.microsoft.com/defaul...US;Q236605
Q231880 BUG: Import Wizard Fails if Excel File Open During Import/Export
http://support.microsoft.com/defaul...S;Q231880t
Q207446 BUG: Cannot Import Excel 97 Spreadsheet with 256 or More Columns
http://support.microsoft.com/defaul...US;Q207446

XML

Q285891 HOWTO: Use VB or ASP to Create an XML Spreadsheet for Excel 2002
http://support.microsoft.com/defaul...US;Q285891
Q278976 HOWTO: Transform Excel XML Spreadsheet for Server-Side Use
http://support.microsoft.com/defaul...US;Q278976

XML - .Net-specific
-
Q319180 HOWTO: Transform Dataset to Spreadsheet XML for Microsoft Excel
http://support.microsoft.com/defaul...US;Q319180
Q307021 HOW TO: Transfer XML Data to Microsoft Excel 2002 with VB .NET
http://support.microsoft.com/defaul...US;Q307021

-

This list is maintained and updated from time to time by DOUGLASL.
The main contributors of content are DOUGLASL (12 articles) and LORITU (10).
-

Disclaimer: This document is provided "AS IS" with no warranties, and
confers no rights.

-Doug
Douglas Laudenschlager
Microsoft SQL Server documentation team
Redmond, Washington, USA

This posting is provided "AS IS" with no warranties, and confers no rights.

"Jose Manuel Davila" wrote in message
news:

que tal exporto a excel con esta rutina desde vb6


aqui envia la instruccion para que exporte
var_cnn = "ODBC;DRIVER=SQL Server;SERVER=" & parametros(0) &
";UID=sa;PWD=" & parametros(3) & ";DATABASE=" & parametros(1)

With oWorkBook.ActiveSheet.QueryTables.Add(Connection:=var_cnn, _
Destination:=oWorkBook.ActiveSheet.Cells(4, 1), SQL:=var_sql)
' Actualizamos los datos...
.Refresh
End With
si son bastantes datos todavia no acaba de exportar sin embargo el
sistema sigue su ejecucion en la sigueinte fila


oSheet.Range("D5").Select

'OEXCEL.ActiveWindow.FreezePanes = True
Screen.MousePointer = vbDefault
OEXCEL.DisplayAlerts = False
oWorkBook.SaveAs var_Nombre_Archivo
oWorkBook.Close 'SaveChanges:=True, Filename:=var_Nombre_Archivo
OEXCEL.Quit
Set oWorkBook = Nothing
Set oSheet = Nothing
Set OEXCEL = Nothing


si todavia no termina el archivo se guarda vacio

como podre controlar esto


gracias a todo ayuda ofrecida