• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

import data from a closed work book

i am using the code below to import data from a closed workbook,it works fine however it seems to not work with dynamic ranges.Any suggestions to have it work with dynamic ranges.Thanks.
Private Sub GetData()
    Dim theActiveSheet As Worksheet
    Dim directory As String
    Dim fileName As String
    Dim sheetName As String
    Dim cellRange As String
    directory = "H:\"
    fileName = "TestFile.xls"
    sheetName = "Dummy"
    cellRange = "A1:C24"
    Set theActiveSheet = ActiveWorkbook.Worksheets(sheetName)
    With theActiveSheet.Range(cellRange)
        .FormulaArray = "='" & directory & _
                        "[" & fileName & "]" _
                        & sheetName & "'!" & _
        .Value = .Value
    End With
End Sub
1 Solution

2 Solutions open the work book because dynamic ranges do not work on closed ranges

or use this Workaround by hard-coding the dynamic Range

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 With Me.Names("myDynamicRange")
 .Value = ("=Dummy!" & .RefersToRange.Address)
 End With
 End Sub

 Private Sub Workbook_Open()
 Me.Names("myDynamicRange").Value =
 End Sub

Open in new window

SvgmassiveAuthor Commented:
Rgonzo1971 i  took your advice created a sub my  import time has decrease significantly.Thanks
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now