vba 通过Access VBA将公式写入Excel
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/13123230/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Writing formula into Excel through Access VBA
提问by KSM
I want to insert in "A1" some text "ABC" and the following cell in "B1" an if
statement. However I get only the first entry "ABC" inserted and then an error at FormulaR1C2 "Object doesn't support this property or method"
. I'm not sure I'm using the R1C2
correctly. I was assuming it stood for Row 1 Column 2, can someone help me out.
我想在“A1”中插入一些文本“ABC”和“B1”中的以下单元格一个if
语句。但是,我只插入了第一个条目“ABC”,然后在FormulaR1C2 "Object doesn't support this property or method"
. 我不确定我R1C2
是否正确使用了。我假设它代表第 1 行第 2 列,有人可以帮助我。
Dim Excel_App As Object
Dim strExcel As String
Set Excel_App = CreateObject("Excel.Application")
Excel_App.Visible = True
Excel_App.Workbooks.Add
With Excel_App
.Range("A:B").EntireRow.ColumnWidth = 25
.Range("A2").EntireRow.Font.FontStyle = "Bold"
.ActiveCell.FormulaR1C1 = "ABC"
strExcel = "=IF(A1 = """"," & """EMPTY""" & "," & """FILLED""" & ") "
.ActiveCell.FormulaR1C2 = strExcel
End With
回答by Scott Holtzman
FormulaR1C1
is the method of how the formula is written.
FormulaR1C1
是如何写公式的方法。
Formula
refers to writing a formula in A1 like =B1+C1
.
Formula
是指在 A1 中写一个公式,如=B1+C1
。
To write that same formula using R1C1
notation, you would write =RC[1] + RC[2]
. Furthermore to write =B2+C2
in A1 write this =R[1]C[1] + R[1]C[2]
-> so you can see you are offsetting the columns and rows where you want the formula to return values from.
要使用R1C1
符号编写相同的公式,您可以编写=RC[1] + RC[2]
. 此外,要=B2+C2
在 A1 中写入此=R[1]C[1] + R[1]C[2]
-> 以便您可以看到您正在偏移您希望公式从中返回值的列和行。
What you want to do in your code is offset the place where the formula will be placed, rather than how it's calculating, so you should write this:
你想在你的代码中做的是偏移放置公式的位置,而不是它的计算方式,所以你应该这样写:
.ActiveCell.Offset(,1).Formula = strExcel
Actually, you should get rid of ActiveCell
altogether, unless you absolutely need it.
实际上,ActiveCell
除非您绝对需要,否则您应该完全摆脱它。
I would write your code like this for better, more accurate execution:
我会像这样编写您的代码以获得更好,更准确的执行:
Dim Excel_App As Object
Dim strExcel As String
Dim wkb as Object, wks as Object
Set Excel_App = CreateObject("Excel.Application")
Excel_App.Visible = True
Set wkb = Excel_App.Workbooks.Add
Set wks = wkb.Sheets(1) 'assumes you want first sheet, can modify for sheet index or name
With wks
.Range("A:B").EntireRow.ColumnWidth = 25
'I think this will actually set every row to 25, is that what you want?
.Range("A2").EntireRow.Font.FontStyle = "Bold"
.Range("A1").Value = "ABC" 'don't need to write Value, but just to show you the property
strExcel = "=IF(A1 = """"," & """EMPTY""" & "," & """FILLED""" & ") "
.Range("B1").Formula = strExcel
End With
回答by Brad
FormulaR1C1
is a property which returns the formula of a cell expressed in R1C1
formula style.
FormulaR1C1
是一个属性,它返回以R1C1
公式样式表示的单元格的公式。
You need to reference cells
by using the Workbook.WorkSheet.Range
syntax.
您需要cells
使用Workbook.WorkSheet.Range
语法进行引用。
So first you need to specify the workbook you are working in, which in your case is the workbook added by the statement Excel_App.Workbooks.Add
. Your new workbook is automatically named something like "Book1" and automatically has the default number of worksheets added named "Sheet1" through "Sheetn" where n is the default number of sheets.
因此,首先您需要指定您正在使用的工作簿,在您的情况下是语句添加的工作簿Excel_App.Workbooks.Add
。您的新工作簿会自动命名为“Book1”,并自动添加名为“Sheet1”到“Sheetn”的默认工作表数量,其中 n 是默认工作表数量。
So you're final code to write to that line is
所以你写到那行的最终代码是
Excel_App.Workbooks(1).Worksheets("Sheet1").Cells(1, 1) = "ABC"
Excel_App.Workbooks(1).Worksheets("Sheet1").Cells(1, 2).Formula = "=IF(A1 = """"," & """EMPTY""" & "," & """FILLED""" & ")"