Import Text Issues

Ok I've used the import external data feature to get a text file with all the
core data inserted into my workbook. Unlike the other posts, it's just the
right size. My problem is I have to make this dummy proof for the user.

The text file will always be the same name. In the same directory. On each
computer.

I need to automatically refresh the data without having the message window
come up to select the file. When I record the macro it still gives me the
option of selecting the file. I DON'T need these people having options like
this. (I'm sure you can understand why).

How do I disable the window and tell excel to refresh using the file?

The name of the file is always the same on everyone's pc? Including
Drive/folder and filename?

If yes, then just have your macro point at that filename:

Workbooks.OpenText Filename:="C:\My Documents\excel\yourfilename.txt", ....

If the location is set for each user, but can differ between users...

I put a giant button from the forms toolbar on a worksheet.
I give them a cell to type the filename.
Then tell the user to save it with their filename.

The code double checks to see if they did a nice job typing:

dim TestStr as string
teststr = ""
on error resume next
teststr = dir(worksheets("sheet99").range("FileNameCell").value
on error go to 0

if teststr = "" then
msgbox "Please fix your typing!
exit sub
end if

====

That way the user doesn't have to search for the file each time (well, after
they've spelled it correctly once).


If this doesn't help, you may want to post the snippet of code that's causing
the trouble.

Jacob_F_Roecker wrote:
>
> Ok I've used the import external data feature to get a text file with all the
> core data inserted into my workbook. Unlike the other posts, it's just the
> right size. My problem is I have to make this dummy proof for the user.
>
> The text file will always be the same name. In the same directory. On each
> computer.
>
> I need to automatically refresh the data without having the message window
> come up to select the file. When I record the macro it still gives me the
> option of selecting the file. I DON'T need these people having options like
> this. (I'm sure you can understand why).
>
> How do I disable the window and tell excel to refresh using the file?

--

Dave Peterson

Thanks Dave.

Yes, every user has the same filename on the same directory each on their
computer. The data in each txt file changes.

Thanks again!!!

"Dave Peterson" wrote:

> The name of the file is always the same on everyone's pc? Including
> Drive/folder and filename?
>
> If yes, then just have your macro point at that filename:
>
> Workbooks.OpenText Filename:="C:\My Documents\excel\yourfilename.txt", ....
>
> If the location is set for each user, but can differ between users...
>
> I put a giant button from the forms toolbar on a worksheet.
> I give them a cell to type the filename.
> Then tell the user to save it with their filename.
>
> The code double checks to see if they did a nice job typing:
>
> dim TestStr as string
> teststr = ""
> on error resume next
> teststr = dir(worksheets("sheet99").range("FileNameCell").value
> on error go to 0
>
> if teststr = "" then
> msgbox "Please fix your typing!
> exit sub
> end if
>
> ====
>
> That way the user doesn't have to search for the file each time (well, after
> they've spelled it correctly once).
>
>
> If this doesn't help, you may want to post the snippet of code that's causing
> the trouble.
>
> Jacob_F_Roecker wrote:
> >
> > Ok I've used the import external data feature to get a text file with all the
> > core data inserted into my workbook. Unlike the other posts, it's just the
> > right size. My problem is I have to make this dummy proof for the user.
> >
> > The text file will always be the same name. In the same directory. On each
> > computer.
> >
> > I need to automatically refresh the data without having the message window
> > come up to select the file. When I record the macro it still gives me the
> > option of selecting the file. I DON'T need these people having options like
> > this. (I'm sure you can understand why).
> >
> > How do I disable the window and tell excel to refresh using the file?
>
> --
>
> Dave Peterson
>