INSERTAR EN EXCEL UNA CONSULTA SQL

En Excel tienes la posiblidad de insertar datos en una hoja de cálculo
procedentes de SQL Server usando la herramienta Microsoft Query basada en
ODBC, que por cierto, no se instala de forma predeterminada. Tienes en el
menú de Excel, Datos->Obtener datos externos.

Saludos:

Jesús López
MVP .net

Perdón, no me expliqué bien.

La inserción en access la hago desde ASP con un primer SELECT y luego le
hago un bucle WHILE con un insert para cada ROW del select.

Me preguntaba si me funcionaría igual con una hoja de excel.


"SqlRanger [MVP .NET]" escribió en el mensaje
news:

En Excel tienes la posiblidad de insertar datos en una hoja de cálculo
procedentes de SQL Server usando la herramienta Microsoft Query basada en
ODBC, que por cierto, no se instala de forma predeterminada. Tienes en el
menú de Excel, Datos->Obtener datos externos.

Saludos:

Jesús López
MVP .net

There is more than one method to show or save data from SQL Server in Excel
from ASP.

If you use the INSERT INTO syntax, you can SELECT from SQL directly into an
Excel worksheet without a loop (without WHILE or FOR EACH). See Q257819 in
the list below.

-Doug

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.


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

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

"Jomaweb" wrote in message
news:

Perdón, no me expliqué bien.

La inserción en access la hago desde ASP con un primer SELECT y luego le
hago un bucle WHILE con un insert para cada ROW del select.

Me preguntaba si me funcionaría igual con una hoja de excel.


"SqlRanger [MVP .NET]" escribió en el mensaje
news:
> En Excel tienes la posiblidad de insertar datos en una hoja de cálculo
> procedentes de SQL Server usando la herramienta Microsoft Query basada

en

> ODBC, que por cierto, no se instala de forma predeterminada. Tienes en

el

> menú de Excel, Datos->Obtener datos externos.
>
> Saludos:
>
> Jesús López
> MVP .net
>
>

ya me enviaron la solucion esta excelente, hay les paso la rutina solo la
interpretan

este metodo solo aplica con Excel 2000


Public Sub pro_envia_excel_sql(var_Nombre_Archivo As String,
VAR_NUMERO_CODIGOS As Long, var_sql As String, var_tabla As String)

Dim oExcel As Excel.Application
Dim oWorkBook As Excel.WorkBook
Dim oSheet As Excel.Worksheet, var_ini, var_fin

Set oExcel = New Excel.Application

oExcel.Visible = False
Screen.MousePointer = vbHourglass
If Dir(var_Nombre_Archivo) <> "" Then
Kill var_Nombre_Archivo
End If

Set oExcel = CreateObject("Excel.Application")
Set oWorkBook = oExcel.Workbooks.Add
Set oSheet = oWorkBook.Worksheets(1)

oExcel.Visible = True

var_ini = Time
'Exportar Datos a Excel
rs.Open "Select " & var_sql & " From " & var_tabla & " where
bint_pla_planta_id = " & var_numero_planta & " order by vcha_pro_producto_id
asc", cnn, adOpenStatic, adLockReadOnly

oSheet.Range("A5").CopyFromRecordset rs

'Salvar este libro de Excel

Screen.MousePointer = vbDefault
oExcel.DisplayAlerts = False
oWorkBook.SaveAs var_Nombre_Archivo
oWorkBook.Close 'SaveChanges:=True, Filename:=var_Nombre_Archivo
oExcel.Quit

var_fin = Time

MsgBox var_fin - var_ini

rs.Close
Set oWorkBook = Nothing
Set oSheet = Nothing
Set oExcel = Nothing

Exit Sub
HELL:
MsgBox ERR.Number & ERR.Description & " " & ERR.Source &
ERR.LastDllError

End Sub



Gracias para quien me envio la solucion

Saludos