What is the verbage in VBA to send files to Excel?

[marthasanchez]

I am looking for the verbage used to send files from another application into
excel. I am not sure how to create a recordset to do this. I am using Excel
XP.

[Tom Ogilvy]

Here is some code posted by "Bob" a short time ago:

Sub RunSQL()

' Create a connection object
Dim cnAssyst_Dev As ADODB.Connection
Set cnAssyst_Dev = New ADODB.Connection

' Provide the connection string
Dim strConn As String

'Use the SQL Server OLE DB Provider
strConn = "PROVIDER=SQLOLEDB;"

'Connect to the Pubs database on the local server
strConn = strConn & "DATA SOURCE=CAIRN10;INITIAL CATALOG=SQLDB;"

'Use an integrated login
strConn = strConn & "User Id=USER;Password=PASS;"

'Now open the connection
cnAssyst_Dev.Open strConn

'Get the start and end dates from Sheet 1
Dim startDate As String
Dim endDate As String
startDate = Range("Sheet1!B17")
endDate = Range("Sheet1!B18")


Sheets("Sheet2").Select


'Set the office to be reported on
Dim office As String
office = "OFFICE"

'Provide the string to hold the SQL Command
Dim sqlString As String

'The completed SQL Command
sqlString = sqlString & "SELECT sla.sla_sc, incident.incident_id,
incident.inc_resolve_due, incident.inc_resolve_act, "
sqlString = sqlString & "incident.inc_close_date, incident.inc_status,
usr_group.usr_group_sc, incident.date_logged, "
sqlString = sqlString & "incident.time_to_resolve,
inc_data.total_service_time, incident.inc_resolve_sla, inc_cat.inc_cat_sc "
sqlString = sqlString & "FROM ((((incident INNER JOIN inc_data ON "
sqlString = sqlString & "incident.incident_id=inc_data.incident_id) INNER
JOIN assyst_usr "
sqlString = sqlString & "ON incident.***_usr_id=assyst_usr.assyst_usr_id)
INNER JOIN sla ON "
sqlString = sqlString & "incident.sla_id=sla.sla_id) INNER JOIN inc_cat ON "
sqlString = sqlString & "incident.inc_cat_id=inc_cat.inc_cat_id) INNER JOIN
usr_group ON "
sqlString = sqlString & "assyst_usr.usr_group_id=usr_group.usr_group_id
WHERE sla.sla_sc<>'' AND "
sqlString = sqlString & "usr_group.usr_group_sc='" & office & "' AND
((incident.date_logged>={ts '" & startDate & " 00:00:00'} "
sqlString = sqlString & "AND incident.date_logged<{ts '" & endDate & "
00:00:00'}) OR (incident.inc_close_date>={ts '" & startDate & " 00:00:00'} "
sqlString = sqlString & "AND incident.inc_close_date<{ts '" & endDate & "
00:00:00'})OR(incident.inc_status = 'o')or(incident.inc_status = 'p'))
ORDER BY sla.sla_sc"

' Create a recordset object
Dim rsAssyst_Dev As ADODB.Recordset
Set rsAssyst_Dev = New ADODB.Recordset

With rsAssyst_Dev
' Assign the Connection object
.ActiveConnection = cnAssyst_Dev
' Extract the required records
.Open sqlString

' Copy the records into cell K2 on Sheet 2
Sheet2.Range("K2").CopyFromRecordset rsAssyst_Dev

' Tidy up
.Close
End With

cnAssyst_Dev.Close
Set rsAssyst_Dev = Nothing
Set cnAssyst_Dev = Nothing

End Sub


See Mr. Erlandsen's site for more info:
DAO/ADO
http://www.erlandsendata.no/english/...php?t=envbadac

--
Regards,
Tom Ogilvy


"marthasanchez" wrote:

> I am looking for the verbage used to send files from another application into
> excel. I am not sure how to create a recordset to do this. I am using Excel
> XP.