Prev: Excel's Dialog Box
Next: How do I type the Spanish letter N?
From: khurram saddique on 12 Feb 2005 11:11
i have a table Transcription_temp in sql server2000 i have to import data
from excel daily. My excel file name is today.xls
my excel headings are
Userid Date TID PID Pname Dname Lines Wtype Template Clients


my table header names are

UserID TranscrioptionDate TranscriptionID PatientName
DoctorName LineTranscribed worktype TemplateName
ClientName

i want a macro to connect sql with excel and export the data of excel file
today.xls
to sql server 2000
From: Jamie Collins on 14 Feb 2005 05:52

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.

--

 | 
Pages: 1
Prev: Excel's Dialog Box
Next: How do I type the Spanish letter N?