Importing a text file by vba referring to a cell for file name & file path

**Vivek2705** · 10-14-2017, 03:56 PM

hello everyone!!

I want to import a text file for which the vba code should refer to the cell B1 for File Path & cell B2 for File name.

My relevant extract of the vba code is as under.

Please Login or Register  to view this content.

Please if anyone can help me out of this.

Thanks.

**jindon** · 10-14-2017, 10:18 PM

Perhaps
If path in B1 has path separator at the end or you need to add to it.

Please Login or Register  to view this content.

**Vivek2705** · 10-15-2017, 02:26 AM

Hi Jindon,

I dont have much knowledge of the vba but i just can record the macro well.

I jus copy pasted your above code and got the syntax error. I am not able to figure out the error..

Provide me the exact code which i just need to copy paste in place of my bold text in my code mentioned above.

Thanks.

**jindon** · 10-15-2017, 02:48 AM

What are values in B1 & B2?

**Vivek2705** · 10-15-2017, 02:56 AM

Thanks for the quick response jindon.

A B
1 Text File Path : C:\Users\vivek\Downloads\FORM 26AS PROMPT
2 Text File Name : AAAFP8402D-2016

i.e. cell b1: C:\Users\vivek\Downloads\FORM 26AS PROMPT
& cell b2: AAAFP8402D-2016

Its like this..Path & File name will change with the user & hence i wanted to link the vba to the cell value.

**jindon** · 10-15-2017, 03:01 AM

So no path separator at the end.

Please Login or Register  to view this content.

**Vivek2705** · 10-15-2017, 03:20 AM

vba error screenshot.png

sent you the vba error screenshot

**Vivek2705** · 10-15-2017, 03:22 AM

Excel file cell b1 & b2.png

screenshot of cell b1 & b2 values

**jindon** · 10-15-2017, 03:28 AM

Try change to

Please Login or Register  to view this content.

**Vivek2705** · 10-15-2017, 03:43 AM

screenshot.png

.Refresh BackgroundQuery:=False was highlighted in yellow after i pressed debug button.

**Vivek2705** · 10-15-2017, 03:49 AM

Please Login or Register  to view this content.

This is the entire vba code which i have recorded.

**jindon** · 10-15-2017, 03:49 AM

That would be something like the file you specified is not found.

**Vivek2705** · 10-15-2017, 03:54 AM

It does exist..without giving reference to a cell for path & file name the code runs well..

**jindon** · 10-15-2017, 04:00 AM

You are adding new sheet at the top then how is it possible to have value in B1 & B2???

They should be blank.

**Vivek2705** · 10-15-2017, 05:11 AM

Ohh now i got the issue.. I want to refer the path & file name (cell b1 & b2) from sheet named "main sheet"..
So what changes should be made in the vba accordingly??

**jindon** · 10-15-2017, 05:17 AM

Add 2 lines and change a part.

Please Login or Register  to view this content.

**Vivek2705** · 10-15-2017, 03:45 PM

Untitled.png

I think the code still searches for path in cell B1 of the new sheet inserted and not the "Main Sheet" which already exists

**Vivek2705** · 12-01-2017, 05:21 AM

Heyy Jindon & everyone,

Attached are the excel & Text file for your reference...

kindly provide the solution to it..

Thanks.

**jindon** · 12-01-2017, 05:33 AM

Replace bold line above with the below

Please Login or Register  to view this content.

**Vivek2705** · 12-01-2017, 08:38 AM

Thankss a lot Jindon...it worked finally....

**jindon** · 12-01-2017, 08:54 AM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Importing a text file by vba referring to a cell for file name & file path

LinkBack

Thread Tools

Rate This Thread

Display

Importing a text file by vba referring to a cell for file name & file path

Re: Importing a text file by vba referring to a cell for file name & file path

Re: Importing a text file by vba referring to a cell for file name & file path

Re: Importing a text file by vba referring to a cell for file name & file path

Re: Importing a text file by vba referring to a cell for file name & file path

Re: Importing a text file by vba referring to a cell for file name & file path

Re: Importing a text file by vba referring to a cell for file name & file path

Re: Importing a text file by vba referring to a cell for file name & file path

Re: Importing a text file by vba referring to a cell for file name & file path

Re: Importing a text file by vba referring to a cell for file name & file path

Re: Importing a text file by vba referring to a cell for file name & file path

Re: Importing a text file by vba referring to a cell for file name & file path

Re: Importing a text file by vba referring to a cell for file name & file path

Re: Importing a text file by vba referring to a cell for file name & file path

Re: Importing a text file by vba referring to a cell for file name & file path

Re: Importing a text file by vba referring to a cell for file name & file path

Re: Importing a text file by vba referring to a cell for file name & file path

Re: Importing a text file by vba referring to a cell for file name & file path

Re: Importing a text file by vba referring to a cell for file name & file path

Re: Importing a text file by vba referring to a cell for file name & file path

Re: Importing a text file by vba referring to a cell for file name & file path

Thread Information

Users Browsing this Thread

Similar Threads

Populate file path and file name in save as dialog box using text in worksheet

Importing text file. Does entire file get read into memory first?

Arrays referring to other file, cell converts to 'VALUE#!" when opened file ..

VBA Importing Text File makes the file longer to load everytime

Importing a data from a text file - user-defined file

URGENT Save file (not file that working in; path/file name variable) to another directory

[Ask] Import Text file And Call reference path file with Value Range.

Bookmarks

Bookmarks

Posting Permissions