closed workbook

예 :

이제 ID 번호 12345에 John John이라는 이름이 있고 John Knight에 업데이트하려고합니다.

ADO를 사용하면 다음을 시도 할 수 있습니다.

 

Edit1 : 실제로 Recordset을 사용하지 않고이 작업을 수행 할 수 있습니다. 아래 업데이트를 참조하십시오.

 

Sub conscious()

Dim con As ADODB.Connection

Dim sqlstr As String, datasource As String

 

Set con = New ADODB.Connection

datasource = "C:UsersUserNameDesktopTestDataBase.xlsx" 'change to suit

 

Dim sconnect As String

sconnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _

"Data Source=" & datasource & ";" & _

"Extended Properties=""Excel 12.0;HDR=YES"";"

 

With con

.Open sconnect

sqlstr = "UPDATE [Sheet1$] SET [Name] = ""John Knight"" WHERE [ID Number] = 12345"

.Execute sqlstr

.Close

End With

 

Set con = Nothing

End Sub

결과:

 

나는 이것이 당신이 원하는 것이지만 HTH인지 확실하지 않습니다.

 

노트:

 

> Microsoft ActiveX Data Objects X.X Library (초기 바인딩)에 대한 참조를 추가해야합니다.

>하지만 늦은 바인드 (참조 없음)를 사용하여이 작업을 수행 할 수도 있습니다.

> 사용 된 연결 문자열은 Excel 2007 이상용입니다.

> Sheet1은 값을 업데이트 할 대상 시트의 이름입니다.

 

편집 1 : 파일 헤더가없는 경우 수행하는 방법입니다.

 

먼저 연결 문자열 HDR 인수를 NO :로 변경하십시오.

 

sconnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _

"Data Source=" & datasource & ";" & _

"Extended Properties=""Excel 12.0;HDR=NO"";"

그런 다음 SQL 문자열을 다음과 같이 조정하십시오.

 

sqlstr = "UPDATE [Sheet1$] SET F2 = ""John Knight"" WHERE F1 = 12345"

필드 1에서는 F1, 필드 2에서는 F2입니다

 

 

 

 

Sub TransferData()

Dim sourceFile As Variant

 

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

 

sourceFile = "C:\Bel.xls"

 

GetData sourceFile, "Daily Figures", "A13:j102", Sheets("Data - Daily").Range("N2"), False, False

 

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True

 

End Sub

 

Public Sub GetData(sourceFile As Variant, SourceSheet As String, _

SourceRange As String, TargetRange As Range, Header As Boolean, UseHeaderRow As Boolean)

' 30-Dec-2007, working in Excel 2000-2007

' http://www.rondebruin.nl/ado.htm

 

Dim rsCon As Object

Dim rsData As Object

Dim szConnect As String

Dim szSQL As String

Dim lCount As Long

 

' Create the connection string.

If Header = False Then

If Val(Application.Version) < 12 Then

szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & sourceFile & ";" & _

"Extended Properties=""Excel 8.0;HDR=No"";"

Else

szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _

"Data Source=" & sourceFile & ";" & _

"Extended Properties=""Excel 12.0;HDR=No"";"

End If

Else

If Val(Application.Version) < 12 Then

szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & sourceFile & ";" & _

"Extended Properties=""Excel 8.0;HDR=Yes"";"

Else

szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _

"Data Source=" & sourceFile & ";" & _

"Extended Properties=""Excel 12.0;HDR=Yes"";"

End If

End If

 

If SourceSheet = "" Then

' workbook level name

szSQL = "SELECT * FROM " & SourceRange$ & ";"

Else

' worksheet level name or range

szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];"

End If

 

On Error GoTo SomethingWrong

 

Set rsCon = CreateObject("ADODB.Connection")

Set rsData = CreateObject("ADODB.Recordset")

 

rsCon.Open szConnect

rsData.Open szSQL, rsCon, 0, 1, 1

 

' Check to make sure we received data and copy the data

If Not rsData.EOF Then

 

If Header = False Then

TargetRange.Cells(1, 1).CopyFromRecordset rsData

Else

'Add the header cell in each column if the last argument is True

If UseHeaderRow Then

For lCount = 0 To rsData.Fields.Count - 1

TargetRange.Cells(1, 1 + lCount).Value = _

rsData.Fields(lCount).Name

Next lCount

TargetRange.Cells(2, 1).CopyFromRecordset rsData

Else

TargetRange.Cells(1, 1).CopyFromRecordset rsData

End If

End If

 

Else

MsgBox "No records returned from : " & sourceFile, vbCritical

End If

 

' Clean up our Recordset object.

rsData.Close

Set rsData = Nothing

rsCon.Close

Set rsCon = Nothing

Exit Sub

 

SomethingWrong:

MsgBox "The file name, Sheet name or Range is invalid of : " & sourceFile, _

vbExclamation, "Error"

on Error GoTo 0

 

End Sub

 

 

Sub GetRange()

With Range("A1:D50") 'set range to copy from / to.

.Formula = "='C:\E3_Test\[CC_Data.xlsx]AllData'!A1" 'refers to a workbook, sheet and first cell.

'It will put the relative references into the target sheet correctly.

.Value = .Value 'changes formula to value.

End With

End Sub