Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Macro problem copying from closed files

  1. #1
    New Member
    Join Date
    May 2002
    Location
    Charleston,sc
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Alright, I've hit the wall, I have been looking thru the board for about a week and found someone with a similar problem to mine. I want to copy a block of cells from different files, which are closed, to one file so I can do some analysis of this file.(I want to create a phantom master file of the others, so I can extract data and use it in analysises). That being said I have used a macro from here and modified it for my purposes. But, It will only copy the block of cells from the first sheet correctly and places "#Value" in the other cells from the other sheets. I have verified the "#Value" cells are coming from the other sheets but are not displaying the data. If anyone is up for the problem, let me know and I can post the code.

    Thank you,
    EMS

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Location
    Greenwood, SC
    Posts
    677
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Post away!

  3. #3
    New Member
    Join Date
    May 2002
    Location
    Charleston,sc
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sub expermient6()
    Dim sdir As String
    Dim Datarg As Range
    Dim myCells As String
    Dim Files
    Dim x As Variant
    'This is the directory being searched
    sdir = "c:My DocumentsECMAC Analysis"
    ' Location of the cells on sheet 1
    myCells = "Sheet1'!$A$7:$G$21"
    Files = Dir(sdir & "*.xls")
    'Speeding things up
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    x = 7
    On Error GoTo FileError
    Do While Len(Files) > 0
    Range("A" & x, "G" & (x + 15)) = "='" & sdir & "[" & Files & "]" & myCells
    Set Datarg = Range("A7", "G" & (x + 15))
    x = x + 15
    Files = Dir()
    'Copying now
    Application.Calculate
    Datarg.Copy
    Datarg.PasteSpecial
    Loop
    Application.CutCopyMode = False
    Set Datarg = Nothing
    Application.Calculation = xlCalculationAutomatic
    Application.Calculate
    Application.ScreenUpdating = True
    Exit Sub

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Location
    Greenwood, SC
    Posts
    677
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I've got an hour long+ meeting in a few minutes, but here's what I've seen.

    It appears that when the formula:

    ='D:[Book1.xls]Sheet1'!$A$7:$G$21

    is in the same range on the new sheet as it is on Book1.Sheet1, all is OK. When the new sheet range changes, you get the errors.

    I created two dummy workbooks to search through and filled Sheet1 on both with a bunch of data. If I change the formula for
    Book2 to

    ='D:[Book1.xls]Sheet1'!$A$22:$G$37, it pulls in the data from those cells.

    I'll be out of pocket for a while, but in any case, I am not sure what the heck is happening. I'll make another attempt when I get back in.

    Good luck,

    K

  5. #5
    New Member
    Join Date
    May 2002
    Location
    Charleston,sc
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the effort, I think I will be working on it for a while so if you have any thought later let me know. In the mean time I will exlpore you idea, I haven't noticed that to this point yet.

    Thanks,
    EMS

  6. #6
    Board Regular
    Join Date
    Apr 2002
    Location
    Greenwood, SC
    Posts
    677
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aha!!!

    I've done it quite a bit differently, but it does seem to work. The only question is whether you want to keep the references or the values in the new workbook. My way keeps the references, but if you want actual values, it would be simple to select the new range and copy, paste special values.

    Here it is:

    Sub expermient()

    Dim Files
    Dim strCol As String
    Dim intRow As Integer
    Dim sdir As String
    Dim x As Integer
    Dim tempStr As String

    Sheets(1).Select

    'Directory to be searched
    sdir = "d:"

    'Offset
    x = 6

    Files = Dir(sdir & "*.xls")

    Do While Len(Files) > 0

    For i = 1 To 15
    For j = 1 To 7

    tempStr = Chr(64 + j) & i + x
    Cells(i + x, j).Value = "='" & sdir & "[" & Files & "]Sheet1'!" & tempStr
    Next
    Next

    x = x + 15
    Files = Dir()

    Loop

    End Sub

    Let me know if you need any clarification.

    K

  7. #7
    New Member
    Join Date
    May 2002
    Location
    Charleston,sc
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Alright it's getting close and the code is definately shorter than mine. It's returning numbers and getting them from the various files but it's still just keeping the order. I need to get A7:G21 of all sheets(eventually there will be 144 sheets) and put them in columns A7:G"whatever is needed". It's now taking A7:G21 from the first file then taking A21:G35 from the next and so on... But I am getting numbers from the other sheets now, but it's just zeros. Any Ideas?

  8. #8
    New Member
    Join Date
    May 2002
    Location
    Charleston,sc
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    WHOOO HOOO!!! I got it to work. Thank you so much for your help K. The x was on both sides of the equation basically. I just replaced 6 for x in the tempStr. By the way if you have time what does "Chr(64+j) & i+6" do anyway. Once again thanks for your help and this board is awesome!!!!!!!!

    thanks,
    ems

Some videos you may like

User Tag List

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
  •