khurram saddique wrote:
i have a table Transcription_temp in sql server2000 i have to import
data
from excel daily.
i want a macro to connect sql with excel and export the data of
excel file
today.xls
to sql server 2000

The following SQL code may be used for an import or an export:

Sub just_four_lines()
Dim con As Object
Set con = CreateObject("ADODB.Connection")
con.Open _
"Provider=SQLOLEDB.1;Data Source=MYSERVER;" & _
"Initial Catalog=MYDATABASE;User Id=sa;password=;"
con.Execute _
"INSERT INTO Transcription_temp (UserID," & _
" TranscrioptionDate, TranscriptionID, PatientID," & _
" PatientName, DoctorName, LineTranscribed, worktype," & _
" TemplateName, ClientName)" & _
" SELECT UserID, TranscrioptionDate, TranscriptionID," & _
" PatientID, PatientName, DoctorName, LineTranscribed," & _
" worktype, TemplateName, ClientName" & _
" FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0'," & _
"'Excel 8.0;HDR=YES;Database=C:\Today.xls'," & _
"'SELECT Userid AS UserID, [Date] AS TranscrioptionDate," & _
" TID AS TranscriptionID, PID AS PatientID," & _
" Pname AS PatientName, Dname AS DoctorName," & _
" Lines AS LineTranscribed, Wtype AS worktype," & _
" Template AS TemplateName, Clients AS ClientName" & _
" FROM [MySheet$]');"
End Sub

Jamie.

--