使用excel`中的vba宏将现有表值复制到新表 [英] copying existing table values to new table using vba macro in excel`

问题描述

您好我正在尝试使用Select * INTO查询将excel表中的值复制到同一数据库中的新表。提取到excel的数据来自其他表，在编辑excel中的一些列后，我需要将excel表发送回sql数据库并创建新表。



我遇到m代码错误：运行时错误'-2147217900'自动化错误



请帮帮我。我正在使用VBA宏从Excel导入表格值并将其导出到SQL。



这是我的代码

Hello I am trying to copy the values which is in excel sheet to new table in same database using Select * INTO query. The data which is extracted to excel is from other table and after editing some columns in excel i need the excel sheet to be sent back to sql database with new table creation.

I got problem with m code which says error : "Run- time error '-2147217900 ' Automation Error"

Please help me out . I am using VBA Macros to import and export the table values to SQL from Excel.

Here is my Code

Dim adoCN As ADODB.Connection
Dim sConnString As String
Dim sSQL As String
Dim lRow As Long, lCol As Long

sConnString = "Provider=sqloledb;Server=;Database=mycon;User Id=;Password="

Set adoCN = CreateObject("ADODB.Connection")

adoCN.Open sConnString

'Assumes that you have Field1, Field2 and Field3 in columns A, B and C
'For this example we can assume that the data exists on Sheet1, with a header on row
'1 and data in rows 2-11
'Also assume that the fields are defined as character (e.g. varchar or char)
'Text values must be enclosed in apostrophes whereas numeric values should not.

'adoCN.Open

For lRow = 1 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

sSQL = "SELECT * INTO TestTable FROM Sheets(1)$"

***adoCN.Execute sSQL***

Next lRow

adoCN.Close

Set adoCN = Nothing


'On Error GoTo 0

错误：adoCN.Execute sSQL

error at : adoCN.Execute sSQL

推荐答案

*** adoCN.Execute sSQL ***

Next lRow

adoCN.Close

设置adoCN = Nothing


'On Error GoTo 0

" ***adoCN.Execute sSQL*** Next lRow adoCN.Close Set adoCN = Nothing 'On Error GoTo 0

错误：adoCN.Execute sSQL

error at : adoCN.Execute sSQL

看看这里：使用VBA进行Excel-SQL Server导入 - 导出 [ ^ ]

它可能很有帮助;）

Have a look here: Excel-SQL Server Import-Export using VBA[^]
It might be helpful ;)

这篇关于使用excel`中的vba宏将现有表值复制到新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！