This was awesome! Paul do you have plans on other coding subjects like Dax and Python? How you teach VBA, so many others would benefit on your teaching of other topics.
Probably would have been a good time to mention Application.ScreenUpdating, Application.DisplayAlerts, Application.EnableEvents, etc. Depending on the workbooks you are importing these become necessary to handle.
I learned the hard way to set the "Workbooks.Open" line to "UpdateLinks: =False" to avoid having an update external links dialog pop up when you open a file... I was opening files to read them and grab data, but a few had links to other workbooks and they would get stuck there.
Thank you for this great video and for the information provided. Please can you provide us a The fastest method to Copying Data from User Selected Files with out open fils.
what if i want a copy of cell starting from a A16 i tried to this Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row data = sheet.Range("A16" & Lastrow) but it gave me an error from this Public Sub ArrayToRange(ByRef arr As Variant, rg As Range) rg.Resize(UBound(arr, 1) - LBound(arr, 1) + 1, UBound(arr, 2) - LBound(arr, 2) + 1) = arr End Sub sorry for my bad english. english is not my mother language
Please guide me to copy part of an excel file into notepad. For example, I want to copy data shown in A1:D5 of Sheet1 of an excel file into a new notepad on the desktop. Thanks in advance.
I have only just fond your channel, I have been out of VBA programming for a long time now, I was self-taught in the late 90s to about 2008. Since then I have had very little to do with programming as I left that job. Now, I'm wanting to get back into programming with Excel VBA, I have a few projects I want to have a go with, one thing that always did my head in was working with date formats in code, it drove me mad that anyone not using the mm/dd/yyyy format were doomed to live in pain. I have data that comes in as text, mostly with the time included and converting to the correct format to read as dd/mm/yyyy I'm finding hit and miss. Are there any existing videos you've discussed this on? Or is this a topic you have planned? Or is this not the right channel to be asking this question?
@@Excelmacromastery Thanks. Let me tell you that I took your advice about advanced filter as a speedy way to move data. I put into practice one of my reports and it flies!
At 1:36 I get a type mismatch error because 'filename' now contains a string so it can't be compared with a boolean value. Is this because my version of Excel is Office 2007?
@@Excelmacromastery Thank you, that fixed it. I was using your code download and running the mod001_basic module. It declares 'filename' as a string variable. The other modules declare it as a Variant. However, I didn't know that a Variant containing a string could be compared with a boolean value. You learn something new everyday, if you're not careful.
You say "ado"? I assume that wasn't a mis-type, and you use the ADO method to extract without opening the workbook? Formatting can't be applied via ADO unfortunately. You would have to use the first example in the video, but paste with formatting, which is always a bit slower and uglier!
@@ricos1497 i currently copies data without ADO, i would prefer ADO it's way faster than this method as it opens and then closes file which in a huge data set creates a problem.
@@utsavsharda yes, it is way faster, but then you don't get formatting! I always wondered if the formatting would be stored somewhere in the underlying XML for the Excel file, but I've never found it!
Let me know in the comments if you have used GetOpenFilename.
Excellent, as always.
I m using ms access as db. Whenever i open workbook i let User select db and i also show User only see accdb default
ありがとうございます。VBA初学者としてとても勉強になりました
Now I have to create a need to use this new knowledge! Thanks Paul for another useful tool for the toolbelt.
Thanks,
In each video I always learn something new. Thanks Paul.
You're welcome Joao
Thank you so much for all your tips and explanations. I could really learn many things from your useful and interesting videos.
This was awesome! Paul do you have plans on other coding subjects like Dax and Python? How you teach VBA, so many others would benefit on your teaching of other topics.
Elegant explanation. It's always a valuable addition to my existing knowledge
Glad you like it
fantastic, saved me loads of time
Congratulations ! Excellent explanation!
Thanks
Your videos are extremely useful. Thank you for sharing your knowledge.
Can you please explain the xml features of excel and vba in a video?
Excellent.
We're almost there.... The classification algorithm with VBA. 😅
Glad you like it
Parabéns ! Excelente explicação!
Thanks
Yes, i have used it in my project. I have created a new function to get the selected files from it
Thanks for the feedback Arvind
Probably would have been a good time to mention Application.ScreenUpdating, Application.DisplayAlerts, Application.EnableEvents, etc. Depending on the workbooks you are importing these become necessary to handle.
I learned the hard way to set the "Workbooks.Open" line to "UpdateLinks: =False" to avoid having an update external links dialog pop up when you open a file... I was opening files to read them and grab data, but a few had links to other workbooks and they would get stuck there.
This is a good one! 🤩
Thanks
Thank you for this great video and for the information provided. Please can you provide us a The fastest method to
Copying Data from User
Selected Files with out open fils.
You can do using ADO. Check my channel for a video on it.
Paul, thanks for the useful video. But the file is not available ehen I follow the link...
what if i want a copy of cell starting from a A16 i tried to this
Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
data = sheet.Range("A16" & Lastrow)
but it gave me an error from this
Public Sub ArrayToRange(ByRef arr As Variant, rg As Range)
rg.Resize(UBound(arr, 1) - LBound(arr, 1) + 1, UBound(arr, 2) - LBound(arr, 2) + 1) = arr
End Sub
sorry for my bad english. english is not my mother language
Please guide me to copy part of an excel file into notepad.
For example,
I want to copy data shown in A1:D5 of Sheet1 of an excel file into a new notepad on the desktop.
Thanks in advance.
I have only just fond your channel, I have been out of VBA programming for a long time now, I was self-taught in the late 90s to about 2008. Since then I have had very little to do with programming as I left that job. Now, I'm wanting to get back into programming with Excel VBA, I have a few projects I want to have a go with, one thing that always did my head in was working with date formats in code, it drove me mad that anyone not using the mm/dd/yyyy format were doomed to live in pain. I have data that comes in as text, mostly with the time included and converting to the correct format to read as dd/mm/yyyy I'm finding hit and miss. Are there any existing videos you've discussed this on? Or is this a topic you have planned? Or is this not the right channel to be asking this question?
If your text is normal enough to have the first 8 characters be the date, you could always format(left(strVariable,8), "mm/dd/yyyy")
If the date is in there you can use regex to extract it.
Does It work also on Mac Osx ? Thanks!
It should but I haven't tried it.
Hi Paul,
What would you do if you want to copy the format of the source?
Copy and paste?
Regards,
George
Yes, that is correct
@@Excelmacromastery
Thanks. Let me tell you that I took your advice about advanced filter as a speedy way to move data. I put into practice one of my reports and it flies!
If data needs to be copied from a sheet which have blank cells due to which current region didn't work there. How we do that
At 1:36 I get a type mismatch error because 'filename' now contains a string so it can't be compared with a boolean value. Is this because my version of Excel is Office 2007?
Have you declared it as a variant?
@@Excelmacromastery Thank you, that fixed it. I was using your code download and running the mod001_basic module. It declares 'filename' as a string variable. The other modules declare it as a Variant. However, I didn't know that a Variant containing a string could be compared with a boolean value. You learn something new everyday, if you're not careful.
I have the same stuff in my Tools, I need ado to copy data in the same manner with the same formatting. can you do it?
You say "ado"? I assume that wasn't a mis-type, and you use the ADO method to extract without opening the workbook? Formatting can't be applied via ADO unfortunately. You would have to use the first example in the video, but paste with formatting, which is always a bit slower and uglier!
@@ricos1497 i currently copies data without ADO, i would prefer ADO it's way faster than this method as it opens and then closes file which in a huge data set creates a problem.
ADO won't copy formatting. Just data.
I have videos on ADO - you can search for them on my channel.
@@utsavsharda yes, it is way faster, but then you don't get formatting! I always wondered if the formatting would be stored somewhere in the underlying XML for the Excel file, but I've never found it!
How to create monthwise total in column with using vba
Best
I like what he has to teach, but he talks to fast for me to pick up what he's putting down.🤔