Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Import Data from Closed Workbook

This is a discussion on Import Data from Closed Workbook within the Excel Questions forums, part of the Question Forums category; Hi all, I have hit a wall as to create an open event to import data from another workbook. This ...

  1. #1
    New Member
    Join Date
    Apr 2008
    Posts
    24

    Default Import Data from Closed Workbook

    Hi all,

    I have hit a wall as to create an open event to import data from another workbook. This process has to be automated.

    Any ideas on where to start?

  2. #2
    Board Regular yytsunamiyy's Avatar
    Join Date
    Mar 2008
    Location
    Berlin, Germany
    Posts
    957

    Default Re: Import Data from Closed Workbook

    something like workbooks.open "C:\YOURPATH\YOURFILENAME.xls" maybe? If you tell us what you want to achieve and how much you know about macros / vba the easier it would be to help.
    ...start every day as if it were on purpose...

    Avatar(c): Dilbert.com

  3. #3
    Board Regular
    Join Date
    Jun 2007
    Posts
    50

    Default Re: Import Data from Closed Workbook

    it's not possible to pull in data from a closed Workbook (without opening it). The code below is a workaround.

    It places an IF formula in the same range as the UsedRange of the closed Workbook (Book1.xls) within the open Workbook (Book2.xls) and pulls in the data from the closed Workbook (Book1.xls). If the cell it is referencing is blank, it puts an #N/A in it's place. I then use the SpecialCells Method to delete all #N/A errors. Last of all it changes all formulas to Values only.

    You first put some simple code in Book1.xls (the closed Workbook) that will give you the exact area Address of the UsedRange on Sheet1. You must place this in the Private Module of ThisWorkbook i.e Workbook_BeforeSave.


    Private Sub Workbook_BeforeSave _
    (ByVal SaveAsUI As Boolean,Cancel As Boolean)
    'Put in the UsedRange Address of Sheet1 Book1.xls (this workbook)
    Sheet2.Cells(1, 1) = Sheet1.UsedRange.Address
    End Sub
    --------------------------------------------------------------------------------
    Now in the Workbook you want to pull the data in (Book2.xls), place this code in a Standard Module.
    --------------------------------------------------------------------------------

    Sub PullInSheet1()
    'Pulls in all data from sheet1 of a closed workbook.

    Dim AreaAddress As String

    'Clear sheet ready for new data
    Sheet1.UsedRange.Clear
    'Reference the UsedRange Address of Sheet1 _
    in the closed Workbook.
    Sheet1.Cells(1, 1) = "= 'C:\My Documents\myfolder\" _
    & "[Book1.xls]Sheet2'!RC"
    'Pass the area Address to a String
    AreaAddress = Sheet1.Cells(1, 1)
    With Sheet1.Range(AreaAddress)
    'If the cell in Sheet1 of the closed workbook is not _
    empty the pull in it's content, else put in an Error.
    .FormulaR1C1 = "=IF('C:\My Documents\myfolder\" _
    & "[Book1.xls]Sheet1'!RC="""",NA(),'C:\My Documents\" _
    & "myfolder\[Book1.xls]Sheet1'!RC)"
    'Delete all Error cells
    On Error Resume Next
    .SpecialCells(xlCellTypeFormulas, xlErrors).Clear
    On Error GoTo 0
    'Change all formulas to Values only
    .Value = .Value
    End With
    End Sub

  4. #4
    New Member
    Join Date
    Apr 2008
    Posts
    24

    Default Re: Import Data from Closed Workbook

    This is what I currently have. Everything work but I do know know why i get an overflow error @ 'y=y+1'.


    Code:
    Sub Import_Removal_Data()
    Dim wb As Workbook
    Dim path As String
    Dim x, y As Integer
    Dim temp1, temp2 As Variant
     
    Application.ScreenUpdating = False
    path = "D:\Documents and Settings\xiae1703\Desktop\Project\"
    Set wb = Workbooks.Open(path & "Fleet Hours and Cycles.xls", True, True)
    'x is the source row
    'y is the main data sheet row where it will be inputed
    x = 2
    y = 1
    With thisworkbook.Worksheets(1)
    
    temp1 = Worksheets(1).Cells(x, 1).Value
    temp2 = Worksheets(1).Cells(x, 4).Value
     
    c = 0
    While c < 5
    
    If temp1 <> "" Then
    'Sets cells in main workbook equal to cells in other data workbook
    thisworkbook.Worksheets(1).Cells(y, 1) = wb.Worksheets(1).Cells(x, 1)
    thisworkbook.Worksheets(1).Cells(y, 2) = wb.Worksheets(1).Cells(x, 2)
    y = y + 1
    End If
    x = x + 1
    If temp2 = "" Then
        c = c + 1
    Else
        c = 0
    End If
    If c > 5 Then wb.Worksheets(1).Activate
    
    Wend
    End With
    wb.Close False
    Set wb = Nothing
    Application.ScreenUpdating = True
     
    End Sub

  5. #5
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    65,648

    Default Re: Import Data from Closed Workbook

    Try this.
    Code:
    Dim x As Long 
    Dimy As Long
    If posting code please use code tags.

  6. #6
    New Member
    Join Date
    Apr 2008
    Posts
    24

    Default Re: Import Data from Closed Workbook

    No dice.

    I get the following error upon that move:

    Run-time error '14404':
    Application-defined or object-defined error

  7. #7
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    65,648

    Default Re: Import Data from Closed Workbook

    That's nothing to do with the change I suggested.

    It must be erroring now for some other reason.

    Which line is highlightes when it happens?
    If posting code please use code tags.

  8. #8
    New Member
    Join Date
    Apr 2008
    Posts
    24

    Default Re: Import Data from Closed Workbook

    y=y+1

  9. #9
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    65,648

    Default Re: Import Data from Closed Workbook

    Well the only thing wrong with what I posted is a slight typo, and I thought you would pick that up.

    But that would cause a syntax error not a runtime error which is what you are getting now.
    Code:
    Dim x As Long
    Dim y As Long
    If posting code please use code tags.

  10. #10
    MrExcel MVP erik.van.geit's Avatar
    Join Date
    Feb 2003
    Location
    Belgium 3272 Testelt
    Posts
    17,765

    Default Re: Import Data from Closed Workbook

    Hi,

    To get data from a closed workbook, see the following link.
    http://www.vbaexpress.com/kb/getarticle.php?kb_id=454

    A long time ago Nimrod suggested this trick:
    Code:
    Sub test() 
    GetValuesFromAClosedWorkbook "C:\folder\anotherfolder", "Book1.xls","Sheet1", "A1" 
    End Sub
     
    Sub GetValuesFromAClosedWorkbook(fPath As String, _ 
    fName As String, sName, cellRange As String) 
    With ActiveSheet.Range(cellRange) 
    .FormulaArray = "='" & fPath & "\[" & fName & "]" _ 
    & sName & "'!" & cellRange 
    .Value = .Value 
    End With
    End Sub
    (didn't test this)
    kind regards,
    Erik

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com