CODEWITHSUNDEEP
vbaexcel
rorance_
rorance_

Reputation: 369

Declaring a variable that works with Workbooks()

I'm looking to build a macro in one workbook (the 'master' workbook), which will grab code from other workbooks on my computer and paste it into the master workbook.

I'm new to this, but I understand that the workbooks have to be open before you can select ranges, copy data etc.

What I'd like to be able to do, is assign the path of all my workbooks, to variables, so that I can call them/read/write to them easily. What I'm trying to do is illustrated in the code below

Sub MasterMacro()

    Dim MasterFile, File1 As Workbook

    Set File1 = Workbooks.Open("D:\test\folder1\file1.xlsx")

    Workbooks(File1).Range("A1").Value = "Help!"

End Sub

However, this produces a Run Time Error '13' - Type mismatch when it tries to change the value of A1.

Upvotes: 0

Views: 119

Answers (1)

paul bica
paul bica

Reputation: 10715

To fix your code update the last line to:

File1.Worksheets("Sheet1").Range("A1").Value = "Help!"

More details:

Option Explicit

Private Const WBM_PATH  As String = "D:\test\folder1\"      'Module-level constant
Private Const WBM_NAME  As String = "file1.xlsx"

Private Const WBM_ERR   As String = "Unable to open Master file"
Private Const BR        As String = vbCrLf & vbCrLf

Private wbM As Workbook, wsM As Worksheet, urM As Range     'Module-level variables

Public Sub MasterMacro()
    If wbM Is Nothing Then
        If Len(Dir(WBM_PATH, vbDirectory)) > 0 Then             'If folder exists
            If Len(Dir(WBM_PATH & WBM_NAME)) > 0 Then           'If file exists
                Set wbM = Workbooks.Open(WBM_PATH & WBM_NAME)   'Set all Master objects
                Set wsM = wbM.Worksheets("Sheet1")
                Set urM = wsM.UsedRange
                urM.Cells(1, 1).Value2 = "Test"                 'Set text in Cell A1

                'copy value from file1.xlsx into the file executing the code
                ThisWorkbook.Worksheets("Sheet1").Range("A1").Value = urM.Cells(1, 1)

                'copy a new value from original file into file1.xlsx
                ThisWorkbook.Worksheets("Sheet1").Range("A2") = "Test2"
                urM.Cells(2, 1) = ThisWorkbook.Worksheets("Sheet1").Range("A2")

                wbM.Close True  'close file1.xlsx, saving changes
            Else
                MsgBox "File '" & WBM_PATH & WBM_NAME & "' doesn't exist" & BR & WBM_ERR
            End If
        Else
            MsgBox "Folder '" & WBM_PATH & "' doesn't exist" & BR & WBM_ERR, , WBM_ERR
        End If
    End If
End Sub

Upvotes: 2

Related Questions