使用VB宏修改电子表格

在Excel中,您可能只需编写代码来打开另一个工作表,修改它,然后保存数据。

看见 this tutorial

稍后我必须编辑我的VBA,所以假设这是伪代码,但它应该类似于:

Dim xl: Set xl = CreateObject("Excel.Application")
xl.Open "\\the\share\file.xls"

Dim ws: Set ws = xl.Worksheets(1)
ws.Cells(0,1).Value = "New Value"
ws.Save

xl.Quit constSilent

您可以在一行中打开电子表格:

Workbooks.Open FileName:="\\the\share\file.xls"

Range("A1").value = "New value"

在玩了一段时间后,我发现Michael的伪代码是最接近的,但我是这样做的:

Dim xl As Excel.Application
Set xl = CreateObject("Excel.Application")
xl.Workbooks.Open "\\owghome1\bennejm$\testing.xls"
xl.Sheets("Sheet1").Select

然后,操纵工作表。。。也许是这样:

xl.Cells(x, y).Value = "Some text"

完成后,使用以下行结束:

xl.Workbooks.Close
xl.Quit

如果进行了更改,将提示用户在关闭文件之前保存该文件。也许有一种方法可以自动保存,但这种方法实际上更好,所以我还是保持原样。

谢谢你的帮助!

将以下内容复制到您的 ThisWorkbook

注:您必须更换 Workbook-SheetChange 和 Workbook-SheetSelectionChange 带下划线。前任: Workbook_SheetChange Workbook_SheetSelectionChange

Option Explicit
Dim varPreviousValue As Variant ' required for IsThisMyChange() . This should be made more unique since it's in the global space.


Private Sub Workbook-SheetChange(ByVal Sh As Object, ByVal Target As Range)
  ' required for IsThisMyChange()
  IsThisMyChange Sh, Target
End Sub

Private Sub Workbook-SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
  '  This implements and awful way of accessing the previous value via a global.
  '  not pretty but required for IsThisMyChange()
  varPreviousValue = Target.Cells(1, 1).Value ' NB: This is used so that if a Merged set of cells if referenced only the first cell is used
End Sub

Private Sub IsThisMyChange(Sh As Object, Target As Range)
  Dim isMyChange As Boolean
  Dim dblValue As Double
  Dim dblPreviousValue As Double

  isMyChange = False

  ' Simple catch all. If either number cant be expressed as doubles, then exit.
  On Error GoTo ErrorHandler
  dblValue = CDbl(Target.Value)
  dblPreviousValue = CDbl(varPreviousValue)
  On Error GoTo 0 ' This turns off "On Error" statements in VBA.


  If dblValue > dblPreviousValue Then
     isMyChange = True
  End If


  If isMyChange Then
    MsgBox ("You've increased the value of " & Target.Address)
  End If


  ' end of normal execution
  Exit Sub


ErrorHandler:
  ' Do nothing much.
  Exit Sub

End Sub

如果您希望在此基础上更改另一个工作簿,我会考虑先检查工作簿是否已打开。。。或者更好地设计一个解决方案,可以批量处理所有更改并立即执行。根据你的听力不断更改另一个电子表格可能会很痛苦。