Tutorial Needed: User upload xlsx file, read that file, write to table

**PrimePorkchop** · 10-17-2021, 01:54 AM

I'm sorry for the odd title - I am having trouble putting into words what I want, and I think that's why I can't locate it via Google Search.

What I am wanting to do may not be doable with Excel ... I don't know.

Is anyone aware of a tutorial that would show me how to allow a user to "upload" a xls or xlsx file from a userform. Once they select their file from their HD, I want excel to "read" the file and sort it in a predefined way. The file being uploaded would be very specific, so the formatting would be predetermined, I just want Excel to read that file and spit the data into a table on the worksheet.

Basically, the user could accomplish the same task by copy/pasting from workbook 1 (raw data) into workbook 2 (where the userform exists) - but I need to account for least common denominator, and try to "dummy proof" the process as much as possible...if I entrust them to "copy paste" properly, the liklihood of them making mistakes is greater-than-not.

Can this be done?

**BadlySpelledBuoy** · 10-17-2021, 03:32 AM

Can this be done? Absolutely! In fact, you won't really need a UserForm, you could just have a command button that opens the File Open dialog box and runs a VBA script to do all the importing etc.

If you attach desensitized versions of your source and target workbooks then it will make it easier for us to show you how.

BSB

**PrimePorkchop** · 10-17-2021, 03:55 AM

It's an idea i've had for a while that I haven't even started working on yet -- when I set out to start, I was hoping to find a walk-thru. If one doesn't exist, I could work on making up a "prototype" to post, but this was more an endeavor I wanted to go through myself to learn how to do it, not ask one of you to do it for me :D

**BadlySpelledBuoy** · 10-17-2021, 08:57 AM

OK, this should get you started and should be easy enough to adapt to your files when you put them togther.
Change the line with the file path to reflect the folder the source file(s) will be in. The target file can be saved elsewhere.

When you click the button on the Target file the open dialog box will show. After selecting a file the data will be imported and added to the end of the list.

It will need error handling in case the user opens a different file (not a source file) but I've kept it simple for now.

Hopefully helpful and easy to follow/amend. But let me know if you have any questions on it.

BSB

Please Login or Register  to view this content.

**PrimePorkchop** · 10-26-2021, 04:22 AM

Buoy - I've been experimenting with this - and it is absolutely brilliant. It works so unbelievably well -- You are a damn wizard man. THANK YOU so much

I might have a few questions in customization as I mess around w/ this a bit more... but otherwise, this is absolute perfection

**PrimePorkchop** · 10-30-2021, 12:34 AM

Hello Buoy!

I've been able to make this work fantastically, and Im very grateful for you! Just one quick question -- I am wanting to put a "Check" into the importation process that looks at cell B4 of the file being implemented, and if that cell doesn't equal "Specifically This" then exit the sub with a messagebox.

I've been farting around w/ the "If" statement...but i can't seem the find the perfect place to put it... and im' probably butchering it. Here's my latest attempt, I highlighted the additions I made to your code from above.

Please Login or Register  to view this content.

**BadlySpelledBuoy** · 10-30-2021, 01:27 AM

Give this a whirl...

Please Login or Register  to view this content.

BSB

**PrimePorkchop** · 10-30-2021, 02:51 AM

with this, I get an error if I upload the wrong form. The error seems to be because Excel is looking for wb.Sheets("Sheet1") before it ever gets to the "IF" statement to evaluate B4. So, if the "incorrect file" that gets selected doesn't have a tab named "Sheet1" then it bugs out...

I don't know what terminology you would use to describe this... but... is it possible to have it evaluate the "official" sheet name, instead of the renamed sheet name?

Example -- on a new workbook, in VBA, I see Sheet1(Sheet1)

But if I rename "Sheet1" to "Data Import"

I now see Sheet1(Data Import)

so, for the wb.sheets evaluation line, can we have it look at the "official name" instead of the "renamed name"? (If that makes ANY sense at all?)

**BadlySpelledBuoy** · 10-30-2021, 04:57 AM

If there's a chance the user will open the wrong file then it would probably make more sense to refer to the sheet position rather than a sheet name.
So change this:

Please Login or Register  to view this content.

To this:

Please Login or Register  to view this content.

Amend (1) to (2) if you need data from the second tab, (3) for the third etc.

At least that way if they open a workbook that doesn't have a "Data Import" sheet it won't error.

Does that help?

BSB

**PrimePorkchop** · 10-30-2021, 10:50 PM

Absolutely perfect!

Tutorial Needed: User upload xlsx file, read that file, write to table

LinkBack

Thread Tools

Rate This Thread

Display

Tutorial Needed: User upload xlsx file, read that file, write to table

Re: Tutorial Needed: User upload xlsx file, read that file, write to table

Re: Tutorial Needed: User upload xlsx file, read that file, write to table

Re: Tutorial Needed: User upload xlsx file, read that file, write to table

Re: Tutorial Needed: User upload xlsx file, read that file, write to table

Re: Tutorial Needed: User upload xlsx file, read that file, write to table

Re: Tutorial Needed: User upload xlsx file, read that file, write to table

Re: Tutorial Needed: User upload xlsx file, read that file, write to table

Re: Tutorial Needed: User upload xlsx file, read that file, write to table

Re: Tutorial Needed: User upload xlsx file, read that file, write to table

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] read CSV file and create XLSX file with certain formatting

Use VBA code to select "read-write" in dialog box when a user has exited an open file

Excel file(xlsx or xlsm) auto upload on SharePoint link

[SOLVED] Use text file to hold usernames and then VBA to read the file to check if the user is list

write on an excel file over the network that user only has read-only access to

Write to .xlsx file on older computer

[SOLVED] Read whole file,Match the headers based on specific condition & write to text file

Bookmarks

Bookmarks

Posting Permissions