Hey, i have one doubt. Have you worked with automating importing pdf files to excel? In my case the pdf format is same and while importing it to power query, i select every 6th page which generally have table. I was wondering if there is any way to automate this part? Your videos are amazing btw, i have learned a lot from you and wise owl in my vba journey
2:41 Your videos are always helpful. However, bringing data with ADO from a spreadsheet can be a bit tricky when you have codes in the same column that can be numeric and alphanumeric. Examples: 1234, 1234-2. Nowadays, Power query is what I use to aggregate data.
It would help me to understand situations in which VBA is a better way to filter, group, etc. than Excel arrays functions and pivot tables, especially when storing data in the data model to improve speed.
I input the data into a worksheet labeled "Sales" and input the VBA code into a procedure called "Private Sub FilterData()", but the code generates an Object error at "Set rg = shSales.Range("A1").CurrentRegion", evidently because the "Sales: worksheet hasn't been dimmed and neither has the workbook, unless it is assumed it is "Thisworkbook".
This is a very useful video. Thank you. However, I work with lots of named tables in Excel. I know that referencing cells in named tables differs from referencing cells by range. How does the VBA code change?
Hello ❤ How are you? I hope you're in the best condition. I was looking for a VBA code that uploads files to the One Drive website can you help me with that? Thank you very much
@13:11 you can use a 1-dimensional array as the dictionary value and skip the index to the separate 2-dimensional array, no? Proof-of-concept: Sub test() Dim odict As New Scripting.Dictionary
They are slower when writing to a range because they have to be written individually. But writing to an array from the class moduke and then to the range is reasonably fast.
Let me know if this video is helpful😀
Hey, i have one doubt. Have you worked with automating importing pdf files to excel? In my case the pdf format is same and while importing it to power query, i select every 6th page which generally have table. I was wondering if there is any way to automate this part? Your videos are amazing btw, i have learned a lot from you and wise owl in my vba journey
Yes. Of course it was useful. Thank you for sharing the wisdom
Dear sir what is sdcproduct and sdcquantity used in .cells(i,...)
Is it a dynamic range declared in excel?
Thank you for an excellent Tutorial. My sincere thanks to you for videos on Arrays, Collections, Dictionary and Classes too. I sure learnt a lot.
You're welcome
Thank You. Your videos are always very helpful.
You're welcome
Your videos are extremely helpful, I'm not joking. They solved many problems I faced before. I wish I knew these methods many years ago.
Glad to help
2:41 Your videos are always helpful. However, bringing data with ADO from a spreadsheet can be a bit tricky when you have codes in the same column that can be numeric and alphanumeric. Examples: 1234, 1234-2. Nowadays, Power query is what I use to aggregate data.
It would help me to understand situations in which VBA is a better way to filter, group, etc. than Excel arrays functions and pivot tables, especially when storing data in the data model to improve speed.
I input the data into a worksheet labeled "Sales" and input the VBA code into a procedure called "Private Sub FilterData()", but the code generates an Object error at "Set rg = shSales.Range("A1").CurrentRegion", evidently because the "Sales: worksheet hasn't been dimmed and neither has the workbook, unless it is assumed it is "Thisworkbook".
I had the same problem and overcame it by using Set rg = Worksheets("Sales").Range("A1").CurrentRegion
ShSales is the code name of the worksheet. See excelmacromastery.com/excel-vba-worksheet/#Using_the_Code_Name_of_a_Worksheet
Paul, thanks for another great video.
You're welcome
Thanks , your video always helpful to understand the various VBA concept. ❤❤
You're welcome
Thank you. I've learnt a lot.
You're welcome
Just what I was looking for, great video
Thanks
Thanks
Great but can't get the workbook code.
Hi Paul, at 9:28, I see sdcProduct and sdcQuantity. I do not see that these aren't declared earlier. Can you help me understand this?
They are enums. Variables that store numbers. They make the code more readable.
merci beaucoup 🙂
Awesome!
This is a very useful video. Thank you. However, I work with lots of named tables in Excel. I know that referencing cells in named tables differs from referencing cells by range. How does the VBA code change?
It's easier Robert. You use the DataBodyRange property of the table to get the data range. To include headers use the Range property.
Thank you§
Welcome!
Super!
thank you
Hello ❤
How are you?
I hope you're in the best condition.
I was looking for a VBA code that uploads files to the One Drive website
can you help me with that?
Thank you very much
@13:11 you can use a 1-dimensional array as the dictionary value and skip the index to the separate 2-dimensional array, no? Proof-of-concept:
Sub test()
Dim odict As New Scripting.Dictionary
odict.Add "plums", Array(77, 105)
odict("plums") = Array(odict("plums")(0) + 10, odict("plums")(1) + 13)
Debug.Print odict("plums")(0), odict("plums")(1)
End Sub
Then you have multiple arrays that you need to write to the worksheet individually
Paul, your example for download contains only a pdf file! I'd expected an Excel worksheet.
All the code is available in the Effective Excel VBA course😀
Which Library I have to use for "Microsoft.ACE.OLEDB.12.0" . I can't find this Library in References in MS Excel 365.
Microsoft ActiveX Data objects 6.1 Library
Maybe you can make a video with class objects and why are so slow
They are slower when writing to a range because they have to be written individually. But writing to an array from the class moduke and then to the range is reasonably fast.
Is it possible to use presented solutions (dictionary and ADO) on Mac Excel 365?
The dictionary and ADO use windows libraries. You need alternative libraries for the Mac
Hi Mr Paul Kelly, I'm gabones !