Randomly opening a available workbook

[anuclearbomb]

Hello everyone,

Just started Vba yesterday so please bear with me :D

I have a project that needs to input data from many small files into a master file, lets call the master file ABC here. Inside the small files, the format are exactly the same.

However the name of the small files are all different and the number of files I have to process is not fixed too.

I wrote this:

Sub Test()


Do


ActiveWindow.Activate
Sheets("2. Particulars").Select
Range("D4").Copy


Windows("ABC.xls").Activate
Range("D" & Rows.Count).End(xlUp).Offset(2).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Windows("ABC.xls").ActivatePrevious

ActiveWindow.Close (savechanges = False)

ActiveWindow.ActivateNext


Loop Until Application.Windows.Count = 1
End Sub


My idea is to open ABC and all the small files at the same time. Manually activate a small file and run macro.

My problem now is that the first small file's result is displayed twice, and another random small file's data is omitted.
If I open 1 small file, run the script and close small file, then do the same manually for the other small files, everything works.

I know this is a longgggg message, but please help

 

[Norie]

First, don't open all the files at the same time.

Open them one at a time, copy whatever's needed, close the file and move onto the next one.

That should be quite straightforward but it depends on a few things.

Are all the 'small' files located in a particular directory?

If they are, are they the only files in that directory, or are they the only files of that type, eg xlsx?

 

[anuclearbomb]

Yeah if I open them one by one, it works fine.
the small files location is free for me to manipulate. So I can put them into a folder if needed. Normally I process 20 to 30 if them in one go.

 

[Norie]

Well like I said it's straightforward to do in code.

Here's the basics.

Dim wbSmall As Workbook
Dim strPath As String
Dim strFileName As String

    strPath = "C:\FolderWhereTheSmallFilesRoam"

    strFileName = Dir(strPath & "\" & "*.*")

    Do Until Len(strFileName) = 0

        Set wbSmall = Workbooks.Open(strPath & "\" & strFileName)

        ' do stuff with small workbook

        ' then close it
        wbSmall.Close

        ' then move onto next file

        strFileName = Dir

    Loop

 

[anuclearbomb]

Wow, thanks man. I dont have a comp to try it out now, but basically it opens, process, and close the individual file until all are done, aight?

 

[Norie]

Well not quite, you'll need to add the part that process the files.

I would have done that myself but I couldn't quite figure out the code you posted.

For example, where are you copying from and where are you posting to?

 

[anuclearbomb]

Well not quite, you'll need to add the part that process the files.

I would have done that myself but I couldn't quite figure out the code you posted.

For example, where are you copying from and where are you posting to?

I would try to figure how to do the whole process once get back to work tomorrow. I am coping some info from the small files to the masterfile, abc.Really appreciate your code

 

[Norie]

I don't think it'll be too complicated, not from looking at the code you posted.

Just make sure you refer to things properly so the correct data is copied to the correct place.