Trying to import CSV directly to XLSM workbook

[dovermac]

I have a simple userform I created. It has a drop down with 10 tab names as options in a macro enabled workbook. The second box is a textbox with a "Open" command button allowing the user to navigate and open a CSV file and populate that textbox with the pull path and filename. Is it possible that when the user clicks on the OK button, that the CSV file located in the textbox is "converted" to xlsm and then "appended" to whichever tab the user selected in the top box of the macro enabled workbook?

[Hawkeye16]

Short answer is yes. A better thread title would be appreciated I bet.

If you have the path and file name you can open the CSV and just copy the information to whatever tab is specified.

[zbor]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[dovermac]

So just copying the entire file contents to a tab in a macro enabled workbook will convert it automatically?!?

[Hawkeye16]

Much better title.

If you open the CSV in excel to do the copying it should automatically convert it to an excel format, just without any formatting on the information. You can test this by opening the CSV you are thinking about in excel and see for yourself what it looks like. I would guess it is close to what you are looking for as a final result (hopefully).

If you were importing a txt file you would need to do a text to columns conversion but CSV is a file format that excel knows how to handle since it is always comma delimited (I assume as it is what the name implies)

[dovermac]

I won't be opening the file in Excel. Remember, I have a button I created on the View ribbon that the user clicks on. They will populate the two boxes, click ok and the data should append to the end of whatever tab they select. That's the hope. So my code will have to be variable driven, as it will always be different. Like take textbox2 copy to combobox1.value...whatever. I'm a vba novice clearly :-)

[dovermac]

I found this code and I'm going to try and tweak it to fit my situation. The "test.csv" cannot be literal, but should be something with textbox2.value. I pasted in my wb name and this will need to append to the existing tab selected so I have to write to the last blank row. I'm trying to find that as I think I have that code somewhere. I think I have to incorporate this line:
LastRow = .Cells(.Rows.Count, aCol).End(xlUp).Row
Is this a good start for the scenario?

Please Login or Register  to view this content.

[Hawkeye16]

That code is opening the CSV in Excel btw

For last row I usually use Range("A" & rows.count).end(xlup).row if I know that, in this case, column A is always populated. Otherwise I will use something like ActiveSheet.usedrange.rows.count

That seems like a good start to me, let us know if you run into any roadblocks.

I would personally do something like this (untested)

Please Login or Register  to view this content.

[dovermac]

Well, if this code is for opening csv and not copying its contents: roadblock :-) Keep in mind, I never even heard of vba before 6 weeks ago.

Please Login or Register  to view this content.

Is this better?

[dovermac]

@Hawkeye: I have seen similar codings, but mine has to be variable centered for my form. For example, the textbox2.value (csv file) will be appended to tab selection in combobox1.value. Does that make sense?

[dovermac]

If this helps, here is my UserForm code without the OK button portion. I don't have a URL to a picture or I would show you that too.

Please Login or Register  to view this content.

[Hawkeye16]

I think you would just use something like... (I believe the whole path gets put into textbox2 and not just the file name right?)

Please Login or Register  to view this content.

instead of

Please Login or Register  to view this content.

When you say workbooks.open(filename) it is opening that file with Excel as a workbook. This is a good thing in this case as it makes the data transfering easier.

[dovermac]

I don't understand how

Please Login or Register  to view this content.

will write to the combobox1 value and the macro enabled workbook is not specified. Doesn't it need to be? Just trying to follow the logic.

[Hawkeye16]

The notes in this will be easier to read if you paste this code into your VBA editor. If you still have questions then I may have misunderstood what you were trying to do.

Please Login or Register  to view this content.

edit: I see what you were saying, this is changed to do that although I do not work with combo box much so I am not positive this is how you pull the value from it

[dovermac]

Ok, I can follow what you're code is doing. The workbook will be open so I guess it doesn't really need to be defined literally anywhere. However, the sheetname will be specified from combobox1. There are 10 possibilities. I would think I should specify the variable combobox1.value in place of sheetname, right? This would ensure the data gets written to the right tab. I have attached a picture of my form so hopefully it makes sense.

[Hawkeye16]

Correct, I have edited the code above so it should be closer to what you would finally end up with.

[dovermac]

I'm getting an expected end sub error when running the form. It occurs on the first line with the OKButton_click

Please Login or Register  to view this content.

[Hawkeye16]

You can't have a sub within a sub you need to get rid of either the first and last row or the second and second to last rows

[dovermac]

Ok now I'm getting run time error 424 in regards to the set csvwkbk line

Please Login or Register  to view this content.

[Hawkeye16]

The .name is wrong since it is not that kind of variable. Sorry about that.

Set csvWkbk = Activeworkbook

This should work since It was opened the line before.


Sent from my iPhone using Tapatalk

[dovermac]

No, I appreciate your help. I'm very grateful. I'm getting subscript out of range 9 on this line:
lastRow = wkbk.Sheets(shtName).Range("A" & Rows.Count).End(xlUp).Row

[Hawkeye16]

That would almost have to be due to not having a tab with the same name as you have in the variable shtname.

Do you know how to insert a breakpoint in vba? If so you can put on there, run the macro and then check the variable names to make sure everything matches. It starts getting difficult to debug without running here.


Sent from my iPhone using Tapatalk

[dovermac]

You are correct, that's the problem. I'm going to make a tab to match real quick and try this again.

[dovermac]

Wow, it worked! I guess the only other issue that might crop up is formatting because I'm sure it may vary from one source to another. Do you know how I can verify that this tab is xlsm?

[Hawkeye16]

I don't understand your last question but the good thing about csv is that they are guaranteed to not have any formatting at all. So you know it will not have any type of format.


Sent from my iPhone using Tapatalk

[dovermac]

Ok scratch that. The only other thing to woory about is importing the csv file from the server. Otherwise, it's done. Thank you so much your help!!!!!

[Hawkeye16]

No problem. Glad it worked out.


Sent from my iPhone using Tapatalk

[dovermac]

Take the rest of the day off, you earned it. :-)

[Hawkeye16]

Already out drinking


Sent from my iPhone using Tapatalk

[dovermac]

Hey Hawkeye, I can import to blank tabs, but trying to actually append to a last row gets me a run time error 6; overflow. This is the statement that is flagged:

lastRow = wkbk.Sheets(shtName).Range("A" & Rows.Count).End(xlUp).Row

Any idea why?

[Hawkeye16]

From a quick internet search it looks like it's from exceeding the bounds of the variable.

Change dim lastrow as integer

To dim lastrow as long

I am surprised I haven't seen that error before if that's the case! Pretty sure integer is supposed to allow a bigger number than excel allows rows.


Sent from my iPhone using Tapatalk

[dovermac]

and another round on the house!!! :-)