Unknown runtime error VBScripts - Write Formula into Excel

santiago carvajal 0
Viewed 50

i'm trying put a formula in specific cell into a Excel, so i do a link between two excel for do this formula. I have this code, but i dont know, how use it for can obtein it that i will realize. i'm amateur in this area and need to help for can do that. my code is this

enter image description here

Set app = CreateObject("Excel.Application")

app.Visible = True

app.Workbooks.Open("C:\MODERNO\REPORTES\RESUMENFACTURA.XLS")

app.Worksheets(1).Cells(2, 17).formula = "=INDEX([Moderno.XLS]ART015!$J:$J;MATCH(P2;  [Moderno.XLS]ART015!$P:$P;0))"
vbscript excel
1 Answers

Write Formula to Excel

  • Since we are creating a new Excel instance, it is assumed that both files are closed (or at least the destination file).

  • The error is occurring because there is app.Workbooks but there is no app.Worksheets i.e. the 'parent-child' order is Application - Workbook - Worksheet - Range. You cannot skip one. You could have used something like

    app.Workbooks(app.Workbooks.Count).Worksheets(1)...

    but since we want to do other stuff with the workbook, it is easier (more readable) to use a variable (see dwb in the code) or alternatively the With statement.

  • Also, the formula is wrong. You cannot use the source workbook name without its path unless the workbook is open. To open it, you need to know its path. In VBA, you need to use commas instead of semicolons when using the Range.Formula property. To use semicolons, you can use the Range.FormulaLocal property. To allow the path, the file, or the worksheet name to have spaces, you need to use single quotes (') in the formula.

The Code

Option Explicit

WriteFormula

Sub WriteFormula

    Const SourceFolderPath = "C:\MODERNO\REPORTES" ' adjust!
    Const SourceFileName = "Moderno.XLS"
    Const DestinationFolderPath = "C:\MODERNO\REPORTES"
    Const DestinationFileName = "RESUMENFACTURA.XLS" 
    
    Dim DestinationFilePath
    DestinationFilePath = DestinationFolderPath & "\" & DestinationFileName

    Dim app: Set app = CreateObject("Excel.Application")
    app.Visible = True
    
    Dim dwb: Set dwb = app.Workbooks.Open(DestinationFilePath)
    On Error Resume Next
        dwb.UpdateLink dwb.LinkSources
    On Error GoTo 0
    dwb.Worksheets(1).Range("Q2").Formula _
        = "=INDEX('" & SourceFolderPath & "\[" & SourceFileName _
        & "]ART015'!$J:$J,MATCH(P2,'" & SourceFolderPath _
        & "\[" & SourceFileName & "]ART015'!$P:$P,0))"
    dwb.Close True
    
    app.Quit

End Sub
VBasic20080
Related