0

I am struggling with extracting columns of many workbooks into one compilation file where I draw Charts of the compiled data... The struggle is that the file referring isn't working if less I have the workbooks open. Secondly, I have tried opening and copying values (by vba), but that is very slow... And some times neither that is working. Even when I have also used the full filepath and file name (with Dir() and everything...). For whatever reason, this code presented here works much faster, but only when the workbooks are open.

I guess it is partly that I am not familiar with most optimal way of extracting and compiling data from other workbooks... Will be greatfull for any tips! :) The dbug.prints are only for overview that everything runs as intended.

Sub hente_inn_celleverdier_original()
'Filenames are listed here: (165 + i, 8) (H166 and underneath) *Edit: wrote wrong celladdress here (G166), fixed now...*
'In the target files, EP23 and EQ23 tells the number of cells in that column/list extempting: "", blank, etc.
'(7, 263 + 2 * i [= 265]) is the upper left cell in the table which we are filling in the compilation file…
Application.ScreenUpdating = False
For i = 1 To 38 '=Number of series(number of files I am extracting these values from) 

Debug.Print (Cells(165 + i, 8)) ' Prints filename (except part with filetype &".xlsx")
A = Workbooks(Cells(165 + i, 8) & ".xlsx").Sheets(1).Range("EP23")
Debug.Print A' Prints number from EP23.
Cells(7, 263 + 2 * i).Value2 = "='[" & Cells(165 + i, 8) & ".xlsx]CPTU'!EP28"
Cells(7, 263 + 2 * i).Select
Selection.AutoFill Destination:=Range(Cells(7, 263 + 2 * i), Cells(6 + A, 263 + 2 * i)),Type:=xlFillDefault

B = Workbooks(Cells(165 + i, 8) & ".xlsx").Sheets(1).Range("EQ23")
Debug.Print B' Prints number from EQ23.
Cells(7, 264 + 2 * i).Value2 = "='[" & Cells(165 + i, 8) & ".xlsx]CPTU'!EQ28"
Cells(7, 264 + 2 * i).Select
Selection.AutoFill Destination:=Range(Cells(7, 264 + 2 * i), Cells(6 + B, 264 + 2 * i)), Type:=xlFillDefault
Next 'i
Application.ScreenUpdating = True
End Sub
Hallvard Skrede
  • 13
  • 5
  • Have you tried the "type" method? It's like creating a dictionary with multiple entries. No copy-paste involved – MikeWasos Jan 18 '23 at 08:40
  • 1
    What about Power Query? – andrewb Jan 18 '23 at 08:44
  • May want to look at what I did here, may not be the best but it worked: https://stackoverflow.com/q/30575923/4961700 – Solar Mike Jan 18 '23 at 08:57
  • This question is likely to be answered with opinions rather than facts and citations. It should be updated so it will lead to fact-based answers. Besides, it may fit better at https://codereview.stackexchange.com/ because your code works but you need to optimize. – Foxfire And Burns And Burns Jan 18 '23 at 09:32
  • Sounds like a possibility @MikeWasos, I found this article about it: https://www.wallstreetmojo.com/vba-type/ But, in with a brief search I couldn't find a simple code involving using ranges in other documents as a data type/variable, Could you (or anyone else) provide an example? :) – Hallvard Skrede Jan 18 '23 at 10:04
  • @andrewb I have "avoided" it as it has already taken a lot of time to learn vba-coding ':) Haha. I am also uncertain how well it will work, if that does not involve opening all files while extracting info from them, and it i s quick, then I should learn it, absolutely. I just thought there should be a really easy and effective way of doing it in vba, as this is something that is rather common, and would benefit a lot of users, I guessed this wuold be something vba would have some optimalized functions/methods for... – Hallvard Skrede Jan 18 '23 at 10:09
  • @FoxfireAndBurnsAndBurns, I didn't know of that webpage under this website. I will try to use it next time. If I understand it correctly. Stackoverflow should be for code that is not working/functions that are requested but uknown for the user (or nonexistent). And optimalizing and review of code should be at that site instead? :) – Hallvard Skrede Jan 18 '23 at 10:14
  • @SolarMike, hmm, thanks for the tips, but the code you suggested is not extracting certain ranges (from sources) to certain ranges (into compilation file) as I am looking for... – Hallvard Skrede Jan 18 '23 at 10:14
  • Then this was based on moving info: https://stackoverflow.com/q/40690592/4961700 – Solar Mike Jan 18 '23 at 10:24
  • Are all source files located in the same folder? The code is using Cells(165 + i, 8) and near the beginning you are saying *"G166 and underneath"*: column 8 is column H. Why don't you explain for the first 2 files what needs to be done. Use A1-style references, e.g. use the source folder path and G166 and .xslx to determine the file path, open the file copy the formula ... to cell(s) ..., autofill ... rows... close the file. Similarly, open the file using G167... – VBasic2008 Jan 18 '23 at 11:15
  • @SolarMike I will take a look. Thanks. Btw, for that problem, if you print to digital pdf first, making a code for printing digital pdfs, you will be able to then afterwards print them in your printer (which is password protected). I guess I am a bit late for that, haha - 6 years... – Hallvard Skrede Jan 18 '23 at 11:24
  • @VBasic2008 You are right, sorry, I will edit to correct address. I misspelled/-remembered when I wrote the question here... It is H a s you said, and that is where my file names are listed (all in the same folder yes, including the compilation file). I can try to make a "clean version in the weekend", and update it here... :) – Hallvard Skrede Jan 18 '23 at 11:26

1 Answers1

0

I'll gladly provide an example as you ask. I don't quite get what you are doing in the code provided, but I'll try to adapt this example

First, you declare the type group on top of everything, including any sub (as you would do with a public variable):

Type WInfo
  WName as string
  WNum as long 'or double, if it is not an integer
end Type

Then you set a special variable inside a sub, with a max of entries you think you'll need (38 in your case, but to be sure, we'll say 200)

Sub hente_inn_celleverdier_original()
Dim WIn(200) As WInfo

You fill the WName variable...

for i = 1 to 38
  WIn(i - 1).WName = Workbooks(Cells(165 + i, 8) & ".xlsx").Sheets(1).Range("EP23")'The entries begin un No.0 with this method
next i

And you can call the value inside the WIn(i - 1).WName with a loop. WIn(0).WName would be the name of the first workbook, WIn(1).WName is the next one... So no need to copy-paste.

Next would be finding a way of getting the info of each workbook. For example, declaring a function to get info from closed workbooks used in [this video][1]. Instead of using your "A" variable, use another for i = 1 loop, filling the WIn(i - 1).WNum variable of the Type group.

MikeWasos
  • 68
  • 7