Importing multiple excel files into one spreadsheet

Woofy_McWoof_Woof

New Member
Joined
Oct 7, 2016
Messages
34
I spoke too soon unfortunately. It just popped up an error when about halfway through importing the files. It says 'someone else is working in the master file, try again later', I'm the only one in the file. I've shut down excel and tried again but same message. It then gives a pop up window with an big red x in it and says 400. Any thoughts?
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,244
Office Version
365
Platform
Windows
Is it possible that somebody else has one of the files in that folder open?
 

Woofy_McWoof_Woof

New Member
Joined
Oct 7, 2016
Messages
34
No, its my own personal drive. I have just shut down excel and my computer to see if it cleared the memory. Still the same issue though, hmm :confused:
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,244
Office Version
365
Platform
Windows
Is the file that contains the code, in the same folder as the files you want to open?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,244
Office Version
365
Platform
Windows
In that case that's the problem as it's trying to open the file in the loop, but you already have it open.
Try
Code:
Sub Woofy()
    Dim Fso As Object, Fileobj As Object
    Dim Ws1 As Worksheet, ws2 As Worksheet
    
    Application.ScreenUpdating = False
    Set Ws1 = ThisWorkbook.Sheets("Optimised")
    Set ws2 = ThisWorkbook.Sheets("Baseload")
    Set Fso = CreateObject("scripting.filesystemobject")
    For Each Fileobj In Fso.GetFolder("C:\Mrexcel\test").Files
        If Fileobj.Name <> "[COLOR=#ff0000]Master.xlsm[/COLOR]" Then
            With Workbooks.Open(Fileobj)
                With .Sheets("Optimised")
                    .Range("A3:AC" & .Range("A" & .Rows.Count).End(xlUp).Row).Copy
                    Ws1.Range("A" & Ws1.Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
                End With
                With .Sheets("Baseload")
                    .Range("A3:AC" & .Range("A" & .Rows.Count).End(xlUp).Row).Copy
                    ws2.Range("A" & ws2.Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
                End With
                Application.CutCopyMode = False
                .Close False
            End With
        End If
    Next Fileobj
End Sub
Change file name in red to suit
 

Woofy_McWoof_Woof

New Member
Joined
Oct 7, 2016
Messages
34
Same issue i'm afraid, its always when it hits row 290. Is there a way to code it to ignore the loop error?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,244
Office Version
365
Platform
Windows
What do you mean by "when it hits row 290"?
 

Woofy_McWoof_Woof

New Member
Joined
Oct 7, 2016
Messages
34
The error message pops up once the imported files in my master file hits row 290, i don't know if its a coincidence though or just part of the looping issue.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,244
Office Version
365
Platform
Windows
Are you saying that as soon as data goes into row 290, you get an error telling you that the file is open?
 

Forum statistics

Threads
1,082,065
Messages
5,362,976
Members
400,702
Latest member
oliviaalx

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top