通过Access VBA将公式写入Excel [英] Writing formula into Excel through Access VBA

问题描述

我想在A1中插入一些文本ABC，而在B1中插入一个如果语句的单元格。但是，我只收到第一个条目ABC，然后在 FormulaR1C2对象不支持此属性或方法中的错误。我不知道我正确地使用了 R1C2 。我假设它代表第1列第2列，有人可以帮助我。

  Dim Excel_App As Object 
 Dim strExcel As String 
设置Excel_App = CreateObject（Excel.Application ）
 Excel_App.Visible = True 
 Excel_App.Workbooks.Add 
使用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 
结束
  

解决方案

FormulaR1C1 是公式如何书面。

公式是指在A1中编写公式，如 = B1 + C1 。

要使用 R1C1 表示法编写相同的公式，您将写入 = RC [1] + RC [2] 。此外，在A1中写入 = B2 + C2 写入 = R [1] C [1] + R [1] C [2] code> - >所以你可以看到你正在抵消你想要的公式返回值的列和行。

你想在你的代码偏移公式的位置，而不是如何计算，所以你应该写这个：

  .ActiveCell .Offset（，1）.Formula = strExcel 
  

其实你应该摆脱 ActiveCell ，除非你绝对需要它。

我会写这样的代码，以获得更好，更准确的执行：

  Dim Excel_App As Object 
 Dim strExcel As String 
 Dim wkb as Object，wks as Object 
 
设置Excel_App = CreateObject（Excel.Application）
 Excel_App.Visible = True 
设置wkb = Excel_App.Workbooks.Add 
设置wks = wkb.Sheets（1）'假设您想要第一张表，可以修改表单索引或名称
 
与wks 
 
 .Range（A：B）。EntireRow.ColumnWidth = 25 
'我认为这将实际上将每一行设置为25，是你想要的？ 
 
 .Range（A2）。EntireRow.Font.FontStyle =Bold
 
 .Range（A1）。Value =ABC需要写价值，但只是为了显示属性
 
 strExcel == IF（A1 =，&EMPTY&，& FILLED&）
 
 .Range（B1）。Formula = strExcel 
 
结束
  

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.

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 

解决方案

FormulaR1C1 is the method of how the formula is written.

Formula refers to writing a formula in A1 like =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.

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.

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 

这篇关于通过Access VBA将公式写入Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！