Update data on a closed workbook?

Option Explicit 

 

Sub Update_Records_Worksheet() 

   Dim cnt As ADODB.Connection 

   Dim cmd As ADODB.Command 

   Dim stCon As String, stSQL As String 

   Dim xlCalc As XlCalculation 

 

   'Change settings in order to increase the performance. 

   With Application 

      xlCalc = .Calculation 

      .Calculation = xlCalculationManual 

      .EnableEvents = False 

      .ScreenUpdating = False 

   End With 

 

 

   'Instantiate the ADODB Objects. 

   Set cnt = New ADODB.Connection 

   Set cmd = New ADODB.Command 

 

   'Create the connection string. 

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

                 "Data Source=c:\Source.xls;" & _ 

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

 

   'Create the SQL query to update the records that meet the condition. 

   stSQL = "UPDATE [Report$] SET Result=20 WHERE Department = 'BB'" 

 

   'Open the connection. 

   cnt.Open stCon 

 

   'Execute the command. 

   With cmd 

      .ActiveConnection = cnt 

      .CommandText = stSQL 

      .Execute 

   End With 

 

   'Close connection. 

   cnt.Close 

 

   'Release objects from memory. 

   Set cmd = Nothing 

   Set cnt = Nothing 

 

   'Restore the settings. 

   With Application 

      .Calculation = xlCalc 

      .EnableEvents = True 

      .ScreenUpdating = True 

   End With 

 

End Sub

Option Explicit
Sub Update_Records_Worksheet()
   Dim cnt As ADODB.Connection
   Dim cmd As ADODB.Command
   Dim stCon As String, stSQL As String
   Dim xlCalc As XlCalculation
   Dim workbookVar, monthVar, issueVar, nameVar, teammemberVar, causeVar As String
   Dim dateVar As String
   
   workbookVar = "C:\DELETEME.XLS"
   monthVar = "January"
   dateVar = "01/28/2008"
   issueVar = "the issue"
   nameVar = "the name"
   teammemberVar = "Team member 1"
   causeVar = "wasnt me"
   
   'Change settings in order to increase the performance.
   With Application
      xlCalc = .Calculation
      .Calculation = xlCalculationManual
      .EnableEvents = False
      .ScreenUpdating = False
   End With
   
   'Instantiate the ADODB Objects.
   Set cnt = New ADODB.Connection
   Set cmd = New ADODB.Command

   'Create the connection string.
   stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                 "Data Source=" + workbookVar + ";" & _
                 "Extended Properties=""Excel 8.0;HDR=YES"";"
   
   'Create the SQL query to update the records that meet the condition.
   stSQL = "INSERT INTO [" + monthVar + "$]"
   stSQL = stSQL + " ([Date],Issue,Name,[Team Member],Cause) "
   stSQL = stSQL + "VALUES ('" + dateVar + "','" + issueVar + "','" + nameVar + "','" + teammemberVar + "','" + causeVar + "')"

   'Open the connection.
   cnt.Open stCon

   'Execute the command.
   With cmd
      .ActiveConnection = cnt
      .CommandText = stSQL
      .Execute
   End With

   'Close connection.
   cnt.Close
   
   'Release objects from memory.
   Set cmd = Nothing
   Set cnt = Nothing

   'Restore the settings.
   With Application
      .Calculation = xlCalc
      .EnableEvents = True
      .ScreenUpdating = True
   End With
 
End Sub

'UNTESTED
Sub Update_Records_Worksheet()

  Dim objRecordset As Object

  Dim stCon As String, stSQL As String
  Dim workbookVar As String, monthVar As String, issueVar As String
  Dim nameVar As String, teammemberVar As String, causeVar As String
  Dim dateVar As String

  workbookVar = "C:\DELETEME.XLS"
  monthVar = "January"
  dateVar = "01/28/2008"
  issueVar = "the issue"
  nameVar = "the name"
  teammemberVar = "Team member 1"
  causeVar = "wasnt me"

  'Create the connection string.
  stCon = Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", workbookVar, _
      ";Extended Properties=""Excel 8.0;HDR=YES"";"), vbNullString)

  'Create the SQL query string
  stSQL = "INSERT INTO [" + monthVar + "$]"
  stSQL = stSQL + " ([Date],Issue,Name,[Team Member],Cause) "
  stSQL = stSQL + "VALUES ('" + dateVar + "','" + issueVar + "','" + nameVar + "','" + teammemberVar + "','" + causeVar + "')"

  Set objRecordset = CreateObject("ADODB.Recordset")
  objRecordset.Open stSQL, stCon
  Set objRecordset = Nothing

End Sub

Sub ChangeDataInSingleCell()
'---------------------------------
'USER: Please make sure you set references to the ADO object Library
'     1. In the Visual Basic window select Tools on the main menu
'     2.  Then from the Tools menu select References...
'     3.  Then click the box for Microsoft ActiveX Data Objects 2.8 or higher
'---------------------------------

Dim objRecordset As ADODB.Recordset
Dim stConn As String
Dim stSQL As String
Dim workbookVar As String
Dim strTarget As String 'Worksheet and cell address - in brackets with sheet name suffixed with a $
Dim newValueVar As Double

'-----
'Note:
'In referencing the sheet and cell to update,
'    we are interested in one cell.  But we "pretend" there is a
'    field header above it - ADO seems to require at least two cells
'-------------------------------------------------------------------
workbookVar = "C:\MyBook.xls"
strTarget = "[Sheet1$E3:E4]"
newValueVar = 2.7182

'Create the connection string.
stConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
workbookVar & ";Extended Properties=Excel 8.0;"
Debug.Print stConn

'Create the SQL query string
stSQL = "SELECT * FROM " & strTarget
Debug.Print stSQL

Set objRecordset = New ADODB.Recordset
Call objRecordset.Open(stSQL, stConn, CursorTypeEnum.adOpenDynamic, _
        LockTypeEnum.adLockOptimistic, CommandTypeEnum.adCmdText)

'Only one record to be updated
    If Not objRecordset.EOF Then
        objRecordset.Fields(0).Value = newValueVar
        objRecordset.Update
    End If

Set objRecordset = Nothing

End Sub