Looking for DAILY content? Join us on my other channel “Coffee With Steve” for daily videos where we discuss Technology, Software Development, Politics, Culture, and many other things. Coffee With Steve: ruclips.net/channel/UCeXAUvo5xxDY_b-lSknPC1A
I talked myself into being responsible for creating a new database for work, without having any clue about this stuff. You, sir, saved my life with videos like this one. They way you quickly explain every line of code and every expression, even if it's not needed, perfect for beginners and good for general learning! Thank you!
This is the best of the best VBA tutorial that I have ever seen. It's step by step and the instructions are clear. You saved my graduation project, thanks man.
I know it's been a few years, but acLink is a good option to create a temporary table if Access is having problems casting the datatype for a particular field. I've found that if you pull it in as a link and then append the data to a table, then destroy the link, it works a lot better. Doing this, you can use a file picker to grab the file name any time you want to import/update
Excellent video! Thank you a lot! One more tip I just figured out: if you want to choose a specific WorkSheet from the excel file, you can write the spreadsheet name with an exclamation mark (e.g., "MyFirstSheet!") instead of leaving the range parameter (8:39) blank.
Hi, Thank you for this video! When I import a Excel spreadsheet, is it possible to import it to an existing table instead of creating a new one as "Me.textFileName"? I also wonder if I can map the excel spreadsheets columns to another in the table, for example the column "Amount" should go to the column "Rate" in the table and if a cell is blank it should put in a value as "0"? I have tried to look in your other videos but can not find something like that, if you already have a video for that I ask, can you please link it? Thanks in advance!
Excellent tutorial Mr. Steve Bishop. I wish you had another one just to append records to that table created first time so that I could do things that way for the office that I work for.
Thanks but one question.. I dont want a bunch of tables so how would I delete the old table if it exists? The method you provided just creates a number at the end of each new table. Thanks!
Thanks for the tutorial. It helps me a lot with my work. I have question, what if we import the same file name? can you show how replace the old file name? another question is that I have existing table in Backend file and want to import from from FrontEnd by appending only the new record. Do you have another VDO tutoring?
Thank you Steve, this is a great video. I would like to know how to avoid creating multiple tables if I load the file twice. In other words if I manipulate the data in excel, how would I overwrite and want to overwrite the previously created imported table in Access?
Thanks for the reply. However, I am not sure how to implement the "Drop Table" code, referencing IF the imported table name are the same. Do you have a video on this topic?
This tutorial helped a lot but one problem the "Badformat" VBA code conflicts when trying to import an Excel currently opened by another user. Where it wouldn't be a problem I can see where this can be later down. Is there a way to check if file is being imported read only
Hi , i have a questions . how write the script module if we need import with specific sheet on file, and browse on folder not file cause we need import many file on folder. thanks.
hi dear steve that was so good , I just have a question. when i click on import Spreadsheet button it works with no error and microsoft access make a table , now if i click on it one more time the table duplicate , how can i prevent this action? how should i change codes? i will preciate if you can help me on this . can you make a video about how to append data from excel to access table in the same way? thanks and sorry for my english
If you change tableName to the actual table name in Access, it appends the data rather than creating a new table. That's how it worked for me anyway 🤷♀️ Be mindful of the existing records already in the table
Great job Steve, what about if I want to append the file directly to an existing table instead of making duplicate tables when you click on the import button, please explain for us. Thank you so much
You can import to a temp table and then use SQL to append the records into your permanent table, then delete from the temp table so it is ready for the next import.
thanks for this tutorial. it's the best tutorial I've ever seen, and it's very helpful. but I have a problem. if I want to import several excel files on the same access table, it only imports data on the first excel and it does not want to import on the next excel. it doesn't add any more data I need. there is a thoughtful view to this issue. thank you.
Hi Mr. Bishop Thank you for all the videos. Could you make a small video explaining how to export access query with begining date and ending date criteria to an existing file in excel. Thanks a lot
Hi, do you have any clips related to unstructured data from a text file? I really want to update the MsAccess app that helps me in the Eu4 game with tons of modifiers that are stored in some files that have structure somehow similar to JSON files. I would really enjoy data mining them using VBA and automatically update my MSAccess tables. Would be really really great if you can do a clip on this topic.
Very nice video and very helpful. However i wanna go one step further.How about if i want to insert data in two tables simultaneously from the same xls file , and these tables have 1 to many relationship? Based on your video, i thought to transfer the whole xls file into a temporary table, and then using VBA code, to insert the data to the parent table, and the data to the child table at the same time with the necessary checks i have to do as well as loop iterations. The ID field in the parent table will be as autonumber, so by inserting the record on that table i will take the ID field and place that as a foreign key to the child table with the necessary columns from the temp table I need the above solution, because in the application i create the data entry will be done automatically by the specific xlss file with the specific column names and all the information mixed up, this never change. Is this way i suggest accurate? Or do you suggest a more appropriate solution? Thank you in advance, and once more thank you for the material you provide.
thank you for this course but when i click browse, display error excution '438' debug in command : diag.Filters.Add "Excel SpreadSheets", "*.xls, *.xlsx" help please
Hi Steve, Very new to VBA - im pretty clued up on Excel Formulas and Power BI DAX but never really dabbled VBA. I have spent a few weeks looking for a CLEAR step by step guide on how to not only write vba to import from Excel to Access but also explanations of each step along the way. I have found this video super informative and wanted to thank you for taking the time to make such an easy to follow guide. I do wonder though - this method "Pulls" data from Excel to Access - do you have any videos on how to Push from Excel to the Access database at all.... Essentially I have around 20 spreadsheets for different customer account ( helpfully the columns are not in the same order either ) and I wish to add these files into a master access DB by adding a macro button onto each file so users just click " send to DB" after completing a line - further caveat, this needs to only "Push" the last used roq and not the entire table. I also need the flexibility to say that on spread sheet 1 Column A = first DB column but on spread 2 column D for example = first DB column. I imagine I would have to write custom ranges into each VB code on each sheet to achieve this,,, Sorry for ranty comment but any assistance would be GREATLY appreciated, Thank you again for the great vid Steve! From Steve!
Another great vidoe Steve. One thing to consider in the filter settings is to include "*xlsm" and"*.xlsb" files. The second is not very common, but many data collection spreadsheets are macro enabled.
Beautifully explained and demonstrated! Let's say you wanted to retain the name of the source worksheet in every record you imported, so you can know where each data point came from? Is there a way to code that into the script?
This a great video! Do you have video that shows how to import data from an Excel file where you only want data from certain columns that may or may not be in series?
Hi thanks for this video first.It works pretty well, but on my excel i ve many folders. How do i choose one folder specific to import in access ?Thanks for your help
I love this! He went fast, so I did not have to sit an wait.... and I could always pause it if I needed to slow it down. The explanations are so GREAT! I am not well versed in vba, so this was perfect! Steve should be a professor! :)
Excellent Video! So if I were wanting to import Excel into an already created table, how would I code it? Also, where would I put the logging code...the very beginning or the end?
Logging can be done anywhere. To import into an existing table, you should follow the same process into a new table. Then run a query to insert all of the records from the new table into the old one.
Hi I am trying to append my SQL queries to any given excel table imported by your Modules, basically I am trying to generalize the code for any table. Please if you have any ideas or any solutions that would be great
Thank you for the video! I've tried using this feature at work, but unfortunately I don't know how I can get it to work with access running via RDP as a remote app. Users are unable to select any files located on their hard drive. Is there anyway around this?
Love the video!!! Question though...We often are replacing files with new data and field headers stay the same. Is there some way to have Access pop up the "Overwrite existing file"? As it stands right now, it imports straight in and adds a character to the end of the file name but this messes with the pre-set query's that look for that specific table name.
+Jeremy Mcquade I WAS going to add in something about this, but I had already taken 30 minutes on this video. Essentially my suggestion is to drop the imported table if it already exists, then re-import. If you have a regular table that already has old data and you want to append new data from Excel to it, then make sure you are importing your Excel as some OTHER table, then using an Insert query to append data to the old table. You can then just drop the imported table after the append query so that you're ready for the next import.
Tremendous help Steve! Thank you so much! Quick question, when the table is imported is there a way for me to do a validation check on that table to make sure there arent certain values? What I would like to happen is the following: 1) User imports file using your method, 2) The Table is then evaluated for any errors, 3) if there are errors kick out the file and make the user do it again, 4) if there are none append it to a larger table. Is this something doable?
Excellent tutorial, but i have one query, in a excel file i have many worksheets, it imports only the first sheet by default i would like to select the sheet i want to import, any code for that would be appreciated best regards
Hi Steve again! I just could not use /CurrentDb.execute "Drop Table " before importing./ in order to delete the already existed table. could you show us this practically?thanks.
Steve. Thank you for posting this video. I get an error on Me.txtFileName. "Method or data member not found." The intellisense does not recognize this property. I have added the reference to Microsoft Office library 16.0. Can you suggest how to resolve this error? Thanks
Excelent demonstartion of VBA in Access. Q If the first row of the excel spreadsheet contains rogue data and the 2nd row contains the column names how do I get access to ignore that first row
Hi Steve. Thank you for this good tutorial. I just wanted to comment on using "for each loop" after file dialog. I have been using the file dialog with allowmultiselect=False without for each loop and it works. Did you test that?
Great explanation - could you add the VBA code that selects the Excel sheet name ? I am trying to write a script that imports a certain excel sheet name.
Hi Steve, First, great tutorial! My question: Is there a way to load multiple excel files, not just one? What should be modified in the code or do you have another video for this? Thank you!
Hi this is very useful to me . Just now I start learning access. I have doubt in this video importing the excel file in separate table for me. Excel data should be import in single table. Can you plz help me on this....!!
Thank you for a thorough explanation. It would be great to see an explanation of appending Excel data to an existing table. Also, I would really like to learn how to append data to multiple tables from a single Excel file. For example, let’s say if I’m storing customer names in one table, in another table I’m storing provinces and a third table contains all orders. Is it possible to append new information to all three tables from a single imported spreadsheet?
+Lena Smith well once you've imported the whole spreadsheet into a table you can use a regular append query to add the data from your new table to the other tables. Just watch my insert/append query videos for more.
is there a video or any recommended links/books where I can use one Spreadsheet into many tables I tried to use this code but I believe i'm misunderstanding the code, Tablename I thought I could put a name of one of my tables and have the data I want imported into a table.right now all I get is a table that is created from the spreadsheet
Thank you for doing this..need to ask from you something you said something about dynamics access reports when i asked how to transfer data in access to word video in my experience access reports does not have horizontal can grow ability..if you can pls do a advanced access reporting video and word import export video..pls cover everything regarding access report if i need to purchase that i will
Hi Steve, every time I import the excel, a new table is created. Please advise how to code such that the content of the access table is deleted and replace by the imported excel file.
Why in Excel 2016, diag.Filters.Add "Excel Spreadsheets", "*.xls, *.xlsx" is giving me Object doesn't support this property or method....Run-time error 438:
Thank you so much for sharing your wisdom! I’ve learned a lot from this, but now i am looking for how i can insert or include a new column along with the imported spreadsheet. in the new column, i would want a certain value inserted. Any help is appreciated. Sorry for bad English :)
how can i overwrite the existing table ? if the same file is imported again , it creates another table numbering the suffix . however i needed to overwrite the existing table if exists
Awesome Video. Clear instructions and explanations! SUBSCRIBED! Keep them coming! I would love to know how multiple front end users can use this import function to import the spreadsheet into a backend so the new sheet is accessible to all users.
Hi, Do you have any Videos on Importing a CSV file? I thought maybe i would use the same method but the problem is that you said "IT HAS to be an Excel file?"
Very impressive, so clear and precise..please can you do a video on exporting data from access to excel, then publish my dashboard to a sharepoint or any other web service
Hi I really like your series and I have learned a lot form you. I have tried to implement the code that you used in this video but I keep getting a Run-time error 424 popping up during the execution of the import button. The debugger highlights the following line: ExcelImport.ImportExcelSpreadsheet Me.txtFileName2, FSO.GetFileName(Me.txtfilename)I was wondering what a possible solution to this might be. Any help would be greatly appreciated. Thanks
Well, you may have a problem with the names of my textboxes not matching your own. Or you didn't add the Scripting as a reference for the FSO object to work.
Hi, that's really awesome . but I got an error run_time error 438 , object does not support this property or method. pointing to diag.add , please help
Nice information.... Could you please help me to open pw protected access file using excel vba. I tried multiple ways Eventhough am not getting.. Can you please
I tried to import an ".xlsx" extension format but it didn't work, the docmd.TransferSpreadsheet didn't work with that extension, Could U please give me a solution???
Dear, This Video Is Amazing ! Thank you so Much. I have a question please, How about if we want to import data and avoid any Duplicate records ? Thank you in advance.
Am just a starter to access and VBA codes, when i try the code diag.Filters.Add "Excel Spreadsheets", "*.xls, *.xlsx, am getting a 438 error as object does not support this property or method, Please let me know where am going wrong so that i can correct myself
Unfortunately the Open File Dialog did not function correctly for me with the coding supplied, I entered, checked and re-checked and compiled the coding and it is exactly the same at displayed, with no errors, but I had the peculiar effect of the dialog box opening twice, the first time it did not fill in the text file but the second time it did. After extensive tracking with '"stops" and "msgbox" commands inserted in different locations in the coding I am afraid that I have not arrived at the solution for this weird effect. I was wondering if anyone else has had the same effect. I am running Office Professional Plus 2016.
Looking for DAILY content? Join us on my other channel “Coffee With Steve” for daily videos where we discuss Technology, Software Development, Politics, Culture, and many other things.
Coffee With Steve: ruclips.net/channel/UCeXAUvo5xxDY_b-lSknPC1A
Hi.. I have one doubt.. How to consolidate multiple workbook and worksheet using ms access..can you guide me
I talked myself into being responsible for creating a new database for work, without having any clue about this stuff. You, sir, saved my life with videos like this one. They way you quickly explain every line of code and every expression, even if it's not needed, perfect for beginners and good for general learning! Thank you!
Great to hear!
This is the best of the best VBA tutorial that I have ever seen. It's step by step and the instructions are clear. You saved my graduation project, thanks man.
I'm glad to hear it!
Fantastic video!!
Best video ever on this topic ... you dont know how much I do appreciate your efforts
I know it's been a few years, but acLink is a good option to create a temporary table if Access is having problems casting the datatype for a particular field. I've found that if you pull it in as a link and then append the data to a table, then destroy the link, it works a lot better. Doing this, you can use a file picker to grab the file name any time you want to import/update
I have a question, what if we get a file that has more than one sheets, how do I get the data from sheet 2 for example?
It's so pleasing. The setuping and mastering softs amazing.
Excellent video! Thank you a lot! One more tip I just figured out: if you want to choose a specific WorkSheet from the excel file, you can write the spreadsheet name with an exclamation mark (e.g., "MyFirstSheet!") instead of leaving the range parameter (8:39) blank.
Thank you soooo much i was wondering how to do that
One of the best tutorials for Access VBA I ever have seen. Very simple and excellent step by step explanation. Great
Fantastic step by step clear explanation - what a great teacher
Very useful for a beginner like me. Nice way to explain each step. I like it. Please keep it up.
Thanks for sharing this valuable knowledge.
I was wondering for this only since long.
Finally I got it. Thanks a lot once again.
Hi, Thank you for this video!
When I import a Excel spreadsheet, is it possible to import it to an existing table instead of creating a new one as "Me.textFileName"?
I also wonder if I can map the excel spreadsheets columns to another in the table, for example the column "Amount" should go to the column "Rate" in the table and if a cell is blank it should put in a value as "0"?
I have tried to look in your other videos but can not find something like that, if you already have a video for that I ask, can you please link it?
Thanks in advance!
This is one of the best videos! Excellent Job
Amazing video. DO you know what code to use If I want to rename my file before importing?
Excellent tutorial Mr. Steve Bishop. I wish you had another one just to append records to that table created first time so that I could do things that way for the office that I work for.
Hands down, great presenter and tutorial
Best tutorial ever for me, I know nothing about Access VBA before this, but the video explains things very clear! So happy! Great Job!
Thanks but one question.. I dont want a bunch of tables so how would I delete the old table if it exists? The method you provided just creates a number at the end of each new table. Thanks!
For few weeks I am watching your videos and this one is really impressive, many many thanks for your extra-ordinary effort.
Thanks for the tutorial. It helps me a lot with my work. I have question, what if we import the same file name? can you show how replace the old file name? another question is that I have existing table in Backend file and want to import from from FrontEnd by appending only the new record. Do you have another VDO tutoring?
Thank you Steve, this is a great video. I would like to know how to avoid creating multiple tables if I load the file twice. In other words if I manipulate the data in excel, how would I overwrite and want to overwrite the previously created imported table in Access?
Just drop the table after you're done working with it, or before. currentdb.execute "DROP TABLE "
Thanks for the reply. However, I am not sure how to implement the "Drop Table" code, referencing IF the imported table name are the same. Do you have a video on this topic?
As I stated, you use currentdb.execute "DROP TABLE " where is the name of the table you want to delete before you import again.
This tutorial helped a lot but one problem the "Badformat" VBA code conflicts when trying to import an Excel currently opened by another user. Where it wouldn't be a problem I can see where this can be later down. Is there a way to check if file is being imported read only
Great video. THANK YOU.
How to append new data to existing table from excel file?
Thanks for the good tutorial. It's really detailed and the way you recorded it's not hard to see.
Thank you Steve.
Hi , i have a questions . how write the script module if we need import with specific sheet on file, and browse on folder not file cause we need import many file on folder. thanks.
one question
if I have a many worksheets and I need to import the worksheet that not be the first of them?
hi dear steve
that was so good , I just have a question. when i click on import Spreadsheet button it works with no error and microsoft access make a table , now if i click on it one more time the table duplicate , how can i prevent this action? how should i change codes? i will preciate if you can help me on this . can you make a video about how to append data from excel to access table in the same way? thanks and sorry for my english
I would also like to learn how to do this.
So like, If i wanted to import data from excel into an existing table in Access, how do i program that?
If you change tableName to the actual table name in Access, it appends the data rather than creating a new table. That's how it worked for me anyway 🤷♀️ Be mindful of the existing records already in the table
Great job Steve, what about if I want to append the file directly to an existing table instead of making duplicate tables when you click on the import button, please explain for us.
Thank you so much
You can import to a temp table and then use SQL to append the records into your permanent table, then delete from the temp table so it is ready for the next import.
I am stuck with some issues selecting another worksheet. Is it possible to get a one-to-one conversation with you?
thanks for this tutorial. it's the best tutorial I've ever seen, and it's very helpful. but I have a problem. if I want to import several excel files on the same access table, it only imports data on the first excel and it does not want to import on the next excel. it doesn't add any more data I need. there is a thoughtful view to this issue. thank you.
Hey, talking about the range of cells in excel to import, how could I select just the columns that I need ?
Hi Mr. Bishop Thank you for all the videos. Could you make a small video explaining how to export access query with begining date and ending date criteria to an existing file in excel.
Thanks a lot
This great! Thank you! Could you also show how to import multiple Excel files, and also only import the sheet (or Tab) the user wanted?
Hi, do you have any clips related to unstructured data from a text file? I really want to update the MsAccess app that helps me in the Eu4 game with tons of modifiers that are stored in some files that have structure somehow similar to JSON files. I would really enjoy data mining them using VBA and automatically update my MSAccess tables. Would be really really great if you can do a clip on this topic.
You are good teacher
Perfect video for what I needed. Very simple, but complete and explains everything clearly. Thanks!
nice video tutorial, sir could you please tell me how to import data alreadey exist table, much apreciated if you explain
Hi Steve, one word of comment: great! Thank you for this explanation and good and simple example.
Very nice video and very helpful. However i wanna go one step further.How about if i want to insert data in two tables simultaneously from the same xls file , and these tables have 1 to many relationship? Based on your video, i thought to transfer the whole xls file into a temporary table, and then using VBA code, to insert the data to the parent table, and the data to the child table at the same time with the necessary checks i have to do as well as loop iterations. The ID field in the parent table will be as autonumber, so by inserting the record on that table i will take the ID field and place that as a foreign key to the child table with the necessary columns from the temp table
I need the above solution, because in the application i create the data entry will be done automatically by the specific xlss file with the specific column names and all the information mixed up, this never change.
Is this way i suggest accurate? Or do you suggest a more appropriate solution?
Thank you in advance, and once more thank you for the material you provide.
This is LEGIT the most helpful access how to I've watched! thank you so much for putting out quality tutorials!
thank you for this course
but when i click browse, display error excution '438'
debug in command : diag.Filters.Add "Excel SpreadSheets", "*.xls, *.xlsx"
help please
How can I add the navigation bar you are using?
A pedagogic, intuitive and good tutorial. Thumbs!
Thank you!
Hi Steve, Very new to VBA - im pretty clued up on Excel Formulas and Power BI DAX but never really dabbled VBA.
I have spent a few weeks looking for a CLEAR step by step guide on how to not only write vba to import from Excel to Access but also explanations of each step along the way.
I have found this video super informative and wanted to thank you for taking the time to make such an easy to follow guide.
I do wonder though - this method "Pulls" data from Excel to Access - do you have any videos on how to Push from Excel to the Access database at all....
Essentially I have around 20 spreadsheets for different customer account ( helpfully the columns are not in the same order either ) and I wish to add these files into a master access DB by adding a macro button onto each file so users just click " send to DB" after completing a line - further caveat, this needs to only "Push" the last used roq and not the entire table. I also need the flexibility to say that on spread sheet 1 Column A = first DB column but on spread 2 column D for example = first DB column. I imagine I would have to write custom ranges into each VB code on each sheet to achieve this,,,
Sorry for ranty comment but any assistance would be GREATLY appreciated,
Thank you again for the great vid Steve!
From
Steve!
Absolutely amazing, thanks Steve for the amazing tutorial
Could you help me to know how to export an excel spreadsheet with some conditions, to know that is the correct excel to be exported?
Another great vidoe Steve. One thing to consider in the filter settings is to include "*xlsm" and"*.xlsb" files. The second is not very common, but many data collection spreadsheets are macro enabled.
+Greg Halliday Great suggestion!
Beautifully explained and demonstrated! Let's say you wanted to retain the name of the source worksheet in every record you imported, so you can know where each data point came from? Is there a way to code that into the script?
This a great video! Do you have video that shows how to import data from an Excel file where you only want data from certain columns that may or may not be in series?
Hi thanks for this video first.It works pretty well, but on my excel i ve many folders. How do i choose one folder specific to import in access ?Thanks for your help
I love this! He went fast, so I did not have to sit an wait.... and I could always pause it if I needed to slow it down. The explanations are so GREAT! I am not well versed in vba, so this was perfect! Steve should be a professor! :)
New to programming. I like the jump around method of coding I saw. Does this work for larger programs?
Jump around method? I don't understand.
Excellent Video! So if I were wanting to import Excel into an already created table, how would I code it? Also, where would I put the logging code...the very beginning or the end?
Logging can be done anywhere. To import into an existing table, you should follow the same process into a new table. Then run a query to insert all of the records from the new table into the old one.
Thanks for the quick reply!
Hey.. How to create and import the data into same table always???
Es lo mejor que he visto en toda mi vida en VBA en macros en Access mil gracias por el ilustrativo.
Great video!! Q. how would you use the same method for appending an existing table?
I would typically not append it to the table directly, but rather run a query afterwards that inserts data from the new table to the old table.
Hi I am trying to append my SQL queries to any given excel table imported by your Modules, basically I am trying to generalize the code for any table. Please if you have any ideas or any solutions that would be great
Thank you for the video! I've tried using this feature at work, but unfortunately I don't know how I can get it to work with access running via RDP as a remote app. Users are unable to select any files located on their hard drive. Is there anyway around this?
You would have to set up shared network folders. I recommend discussing this with your IT.
Love the video!!! Question though...We often are replacing files with new data and field headers stay the same. Is there some way to have Access pop up the "Overwrite existing file"? As it stands right now, it imports straight in and adds a character to the end of the file name but this messes with the pre-set query's that look for that specific table name.
+Jeremy Mcquade I WAS going to add in something about this, but I had already taken 30 minutes on this video. Essentially my suggestion is to drop the imported table if it already exists, then re-import. If you have a regular table that already has old data and you want to append new data from Excel to it, then make sure you are importing your Excel as some OTHER table, then using an Insert query to append data to the old table. You can then just drop the imported table after the append query so that you're ready for the next import.
@@ProgrammingMadeEZ is there a video that shows me exactly how this can be done in VBA.
Tremendous help Steve! Thank you so much! Quick question, when the table is imported is there a way for me to do a validation check on that table to make sure there arent certain values? What I would like to happen is the following: 1) User imports file using your method, 2) The Table is then evaluated for any errors, 3) if there are errors kick out the file and make the user do it again, 4) if there are none append it to a larger table. Is this something doable?
Excellent tutorial, but i have one query, in a excel file i have many worksheets, it imports only the first sheet by default
i would like to select the sheet i want to import, any code for that would be appreciated
best regards
Hi Steve again! I just could not use /CurrentDb.execute "Drop Table " before importing./ in order to delete the already existed table. could you show us this practically?thanks.
thank sir, nicely explained, very clear, understandable explanation of code, why using that code, not the other one.
Excellent lecture
Steve. Thank you for posting this video. I get an error on Me.txtFileName. "Method or data member not found." The intellisense does not recognize this property. I have added the reference to Microsoft Office library 16.0. Can you suggest how to resolve this error? Thanks
Steve: I see from the comments below what the problem was. It's resolved. Thank you!
Excelent demonstartion of VBA in Access. Q If the first row of the excel spreadsheet contains rogue data and the 2nd row contains the column names how do I get access to ignore that first row
Hi Steve. Thank you for this good tutorial. I just wanted to comment on using "for each loop" after file dialog. I have been using the file dialog with allowmultiselect=False without for each loop and it works. Did you test that?
That's what I set it to during the video, but the forloop is a good practice regardless.
Great explanation - could you add the VBA code that selects the Excel sheet name ?
I am trying to write a script that imports a certain excel sheet name.
Hi Steve,
First, great tutorial! My question: Is there a way to load multiple excel files, not just one? What should be modified in the code or do you have another video for this?
Thank you!
Hi this is very useful to me . Just now I start learning access. I have doubt in this video importing the excel file in separate table for me. Excel data should be import in single table. Can you plz help me on this....!!
Thank you for a thorough explanation. It would be great to see an explanation of appending Excel data to an existing table. Also, I would really like to learn how to append data to multiple tables from a single Excel file. For example, let’s say if I’m storing customer names in one table, in another table I’m storing provinces and a third table contains all orders. Is it possible to append new information to all three tables from a single imported spreadsheet?
+Lena Smith well once you've imported the whole spreadsheet into a table you can use a regular append query to add the data from your new table to the other tables. Just watch my insert/append query videos for more.
is there a video or any recommended links/books where I can use one Spreadsheet into many tables I tried to use this code but I believe i'm misunderstanding the code, Tablename I thought I could put a name of one of my tables and have the data I want imported into a table.right now all I get is a table that is created from the spreadsheet
Thank you for doing this..need to ask from you something you said something about dynamics access reports when i asked how to transfer data in access to word video in my experience access reports does not have horizontal can grow ability..if you can pls do a advanced access reporting video and word import export video..pls cover everything regarding access report if i need to purchase that i will
+Gayan Kalinga I'll see what I can do.
THank you
This man really is amazing, I hope more programming and data structure tutorials using c#.
Hi Steve, every time I import the excel, a new table is created. Please advise how to code such that the content of the access table is deleted and replace by the imported excel file.
Just do a CurrentDb.execute "Drop Table " before importing. You will want to check if the table exists first though.
Why in Excel 2016, diag.Filters.Add "Excel Spreadsheets", "*.xls, *.xlsx" is giving me Object doesn't support this property or method....Run-time error 438:
It may not be compatible with Excel, I only wrote this for Access.
Thank you so much for sharing your wisdom!
I’ve learned a lot from this, but now i am looking for how i can insert or include a new column along with the imported spreadsheet. in the new column, i would want a certain value inserted.
Any help is appreciated. Sorry for bad English :)
how can i overwrite the existing table ? if the same file is imported again , it creates another table numbering the suffix . however i needed to overwrite the existing table if exists
Awesome Video. Clear instructions and explanations! SUBSCRIBED! Keep them coming! I would love to know how multiple front end users can use this import function to import the spreadsheet into a backend so the new sheet is accessible to all users.
Hi, Do you have any Videos on Importing a CSV file? I thought maybe i would use the same method but the problem is that you said "IT HAS to be an Excel file?"
Use DoCmd.TransferText acImportDelim
You just made my day bro...great, thanks
👍
Thanks tNice tutorials helps alot man!!
Very impressive, so clear and precise..please can you do a video on exporting data from access to excel, then publish my dashboard to a sharepoint or any other web service
Hi I really like your series and I have learned a lot form you. I have tried to implement the code that you used in this video but I keep getting a Run-time error 424 popping up during the execution of the import button. The debugger highlights the following line: ExcelImport.ImportExcelSpreadsheet Me.txtFileName2, FSO.GetFileName(Me.txtfilename)I was wondering what a possible solution to this might be. Any help would be greatly appreciated. Thanks
Well, you may have a problem with the names of my textboxes not matching your own. Or you didn't add the Scripting as a reference for the FSO object to work.
Hi, that's really awesome . but I got an error run_time error 438 , object does not support this property or method. pointing to diag.add , please help
Nice information....
Could you please help me to open pw protected access file using excel vba. I tried multiple ways Eventhough am not getting..
Can you please
I tried to import an ".xlsx" extension format but it didn't work, the docmd.TransferSpreadsheet didn't work with that extension, Could U please give me a solution???
If you have already imported a table, and then the data changes In the excel spreadsheet, and you import again, will it overwrite the current table.
Very much wonderful video Steve. It was hugely useful. Thank you so much!
Hi Steve, an excellent video. I was looking for exactly same thing and all scenarios I found in a single video ... thanks a lot!
Dear, This Video Is Amazing ! Thank you so Much.
I have a question please, How about if we want to import data and avoid any Duplicate records ?
Thank you in advance.
Great content surpose i what to import to existing table?
Am just a starter to access and VBA codes, when i try the code diag.Filters.Add "Excel Spreadsheets", "*.xls, *.xlsx, am getting a 438 error as object does not support this property or method, Please let me know where am going wrong so that i can correct myself
Compare your code to the work files and see if you can spot the difference.
awesome and to the point
Thanks for the tutorial! it was really useful :)
Is it possible to export many tabs of a single excel file into the same amount of tables?
Yes. You have to specify the range parameter with the page name.
how to do this for an xlsm file. it doesn't want to open it in access
Hi Sir Steve.. what if I import the excel worksheet and has a 5 sheets.. and i need only 1 sheet from this work book?
That's what the range parameter is for.
Unfortunately the Open File Dialog did not function correctly for me with the coding supplied, I entered, checked and re-checked and compiled the coding and it is exactly the same at displayed, with no errors, but I had the peculiar effect of the dialog box opening twice, the first time it did not fill in the text file but the second time it did.
After extensive tracking with '"stops" and "msgbox" commands inserted in different locations in the coding I am afraid that I have not arrived at the solution for this weird effect. I was wondering if anyone else has had the same effect.
I am running Office Professional Plus 2016.