CODEWITHSUNDEEP
vbaexcel
TasTur
TasTur

Reputation: 29

Excel VBA: Can't create sheets on called another Excel file

The goal is to open another Excel file with parameters from first / main file and call macro who creates 1 or n new sheets with data from database, but Excel won't create new sheets in second file and then all other logic fails.

You can find sample code for two files below. When B file is opened manually and called tst() sub, this works, but not when first file opens second file. Workbooks are not protected, I'm using MS Excel 2010.

A_file.xlsm is main file where user calls GetFile to open another file and run ReadParams macro. Code is located in modules.

Sub GetFile(fileName As String)
    Dim filePath, par1, par2, currentUser As String
    Dim targetFile As Workbook

    currentUser = CreateObject("WScript.Network").UserName
    filePath = "C:\Users\" & currentUser & "\Documents\Excel_APPS\"
    par1 = "USE_R_one"
    par2 = "some_val"

    Application.ScreenUpdating = False
    Set targetFile = Workbooks.Open(filePath & "B_file.xlsm")
    Application.Run "'" & targetFile.Name & "'!ReadParams(" & Chr(34) & par1 & Chr(34) & ", " & Chr(34) & par2 & Chr(34) & ")"
    targetFile.Activate
    Application.ScreenUpdating = True
End Sub

B_file.xlsm macros:

Sub ReadParams(s_uno As String, s_duo As String)
    If IsNull(s_uno) Or IsNull(s_duo) Then
        MsgBox "Error occurred.", vbExclamation, "Error"
    Else
        MsgBox "All params are ok, new sheet is coming right after this msg"
    ThisWorkbook.Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Sheet_Data" '<-- THIS WON'T WORK
    End If
End Sub

Sub tst()
    ReadParams "USE_R", "test"
End Sub

Upvotes: 1

Views: 66

Answers (1)

Sam
Sam

Reputation: 5721

The problem is in the line Application.Run... The syntax for this is 

Application.Run "'b.xlsm'!ReadParams", par1, par2

You had your parameters concatenated to the first argument

Upvotes: 1

Related Questions