Hello,
I am linking to many tab delimted text files, which are
not always consistent (i.e.-sometimes columns will be
missing altogether upon refresh, causing problems with
the linked data).

I may have found a solution by using the "send keys"
action Macro. This seems to work perfect all the way up
to the very end, when the final confirmation
message "finished linking table..." appears. I have
tried everything I can think of to either eliminate the
pop-up (set warnings on to "No") and hardcoding the final
{enter} or ~ to emulate clicking "OK". My understanding
is that the final message halts the macro, so a
knowledgebase article suggests moving the {enter} or ~ to
BEFORE the key which causes the confirmation pop-up. I
tried this...still doesn't work. I feel that I am SO
close with this solution. Here are the keys I am sending
after setting warnings to "No":

%fglc:\data\mel\excel stuff for john a\Pitch 35P.txt%
kdnrn%nPitch 35P%fy~

The final "~" is to simply enter "OK" after the
confirmation, but it wont work.

Please help...

Why in the WORLD are you using SendKeys? Wouldn't a simple TransferText
command do? If you need to dynamically supply the file name, do a SendKeys
NoWait of just the file name and the Enter immediately followed by
RunCommand LinkTables. Of course, this would be much easier to do in VBA -
execute a TransferText with a variable file name.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
"John Alexander" <(E-Mail Removed)> wrote in message
news:12d9501c41669$b8df36f0$(E-Mail Removed)...
> Hello,
> I am linking to many tab delimted text files, which are
> not always consistent (i.e.-sometimes columns will be
> missing altogether upon refresh, causing problems with
> the linked data).
>
> I may have found a solution by using the "send keys"
> action Macro. This seems to work perfect all the way up
> to the very end, when the final confirmation
> message "finished linking table..." appears. I have
> tried everything I can think of to either eliminate the
> pop-up (set warnings on to "No") and hardcoding the final
> {enter} or ~ to emulate clicking "OK". My understanding
> is that the final message halts the macro, so a
> knowledgebase article suggests moving the {enter} or ~ to
> BEFORE the key which causes the confirmation pop-up. I
> tried this...still doesn't work. I feel that I am SO
> close with this solution. Here are the keys I am sending
> after setting warnings to "No":
>
> %fglc:\data\mel\excel stuff for john a\Pitch 35P.txt%
> kdnrn%nPitch 35P%fy~
>
> The final "~" is to simply enter "OK" after the
> confirmation, but it wont work.
>
> Please help...
>
>

Because,
as I wrote. All of these txt files could potentially
have a different structure (missing columns). I tried
your transfer text solution. The problem with that is it
relys on a consistent structure. For example, say I have
a tab delimited file with 3 columns, A, B, and C
populated with 1, 2, and 3. When I set this up with a
transfer text macro, it needs a "spec" telling it how to
delimit the columns AND (unfortunately necessary) the
COLUMN NAMES. I need the column names to be dynamically
read every time, since next time, maybe column B will be
missing on the refreshed file. If column "B" is missing
on the refresh, then the transfer text solution (based on
the given spec of A, B, C) will produce a table A, B, C
populated with 1, 3, null (i.e.-the data shifts over a
column). Does this make sense?

So, to answer your other question, it is not really the
filenames that need to be dynamic (there could be 50 or
so, but they can, and should be hardcoded), it is really
the STRUCTURE that needs to be dynamic. Actually, I am
quite surprised that many people do not have the same
problem. I have seen PAGES of code which try to to that
same thing, but I'm not a VBA expert, and I honestly
think my send keys solution is perfect except for that
last ~, which is supposed to close the idiotic
confirmation. Make sense? Or, am I missing something?

Thanks!

>-----Original Message-----
>Why in the WORLD are you using SendKeys? Wouldn't a
simple TransferText
>command do? If you need to dynamically supply the file
name, do a SendKeys
>NoWait of just the file name and the Enter immediately
followed by
>RunCommand LinkTables. Of course, this would be much
easier to do in VBA -
>execute a TransferText with a variable file name.
>
>--
>John Viescas, author
>"Microsoft Office Access 2003 Inside Out"
>"Running Microsoft Access 2000"
>"SQL Queries for Mere Mortals"
>http://www.viescas.com/
>(Microsoft Access MVP since 1993)
>"John Alexander" <(E-Mail Removed)> wrote in
message
>news:12d9501c41669$b8df36f0$(E-Mail Removed)...
>> Hello,
>> I am linking to many tab delimted text files, which are
>> not always consistent (i.e.-sometimes columns will be
>> missing altogether upon refresh, causing problems with
>> the linked data).
>>
>> I may have found a solution by using the "send keys"
>> action Macro. This seems to work perfect all the way up
>> to the very end, when the final confirmation
>> message "finished linking table..." appears. I have
>> tried everything I can think of to either eliminate the
>> pop-up (set warnings on to "No") and hardcoding the
final
>> {enter} or ~ to emulate clicking "OK". My
understanding
>> is that the final message halts the macro, so a
>> knowledgebase article suggests moving the {enter} or ~
to
>> BEFORE the key which causes the confirmation pop-up. I
>> tried this...still doesn't work. I feel that I am SO
>> close with this solution. Here are the keys I am
sending
>> after setting warnings to "No":
>>
>> %fglc:\data\mel\excel stuff for john a\Pitch 35P.txt%
>> kdnrn%nPitch 35P%fy~
>>
>> The final "~" is to simply enter "OK" after the
>> confirmation, but it wont work.
>>
>> Please help...
>>
>>
>
>
>.
>

Well, you say the SendKeys you're trying to do is:

%fglc:\data\mel\excel stuff for john a\Pitch 35P.txt%kdnrn%nPitch 35P%fy~

Let me see if I can figure out what you're attempting to do...

Alt-F drops down the File menu. G selects Get External Data, and L selects
Link Tables - which should open the Link open file dialog. And then you
attempt to "type" in the file name, followed by Alt-K that "clicks" the Link
button, which opens the text import wizard despite the fact that you did not
change the "files of type" box.

The letter D selects Delimited, letter N "clicks" the Next button on the
first panel of the Wizard, letter R says the first row contains column
names, N clicks Next. Alt-N clicks Next again, and you try to type in the
name of the linked table - "Pitch 35P". Alt-F clicks Finish, and you try to
close it out with an Enter.

I think what you're trying to do is get the Import Wizard to ignore the
specification each time and just link the table "as is." I tried a little
test. First, I linked a delimited text file that contained three columns
using the Wizard. Sure enough, it created an import spec with the three
columns. Next, I removed one of the columns from my test file. I then
created a macro to link this file (didn't change the name of the file or the
link name) like this:

Action: TransferText
Transfer Type: Link Delimited
Specification Name: (left blank!)
Table Name: MyTable
File Name: C:\MyFiles\MyTable.txt
Has Field Names: Yes

Works like a champ without all the Sendkeys folderol.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
"John" <(E-Mail Removed)> wrote in message
news:15ba301c41670$8cdd5440$(E-Mail Removed)...
> Because,
> as I wrote. All of these txt files could potentially
> have a different structure (missing columns). I tried
> your transfer text solution. The problem with that is it
> relys on a consistent structure. For example, say I have
> a tab delimited file with 3 columns, A, B, and C
> populated with 1, 2, and 3. When I set this up with a
> transfer text macro, it needs a "spec" telling it how to
> delimit the columns AND (unfortunately necessary) the
> COLUMN NAMES. I need the column names to be dynamically
> read every time, since next time, maybe column B will be
> missing on the refreshed file. If column "B" is missing
> on the refresh, then the transfer text solution (based on
> the given spec of A, B, C) will produce a table A, B, C
> populated with 1, 3, null (i.e.-the data shifts over a
> column). Does this make sense?
>
> So, to answer your other question, it is not really the
> filenames that need to be dynamic (there could be 50 or
> so, but they can, and should be hardcoded), it is really
> the STRUCTURE that needs to be dynamic. Actually, I am
> quite surprised that many people do not have the same
> problem. I have seen PAGES of code which try to to that
> same thing, but I'm not a VBA expert, and I honestly
> think my send keys solution is perfect except for that
> last ~, which is supposed to close the idiotic
> confirmation. Make sense? Or, am I missing something?
>
> Thanks!
>
> >-----Original Message-----
> >Why in the WORLD are you using SendKeys? Wouldn't a
> simple TransferText
> >command do? If you need to dynamically supply the file
> name, do a SendKeys
> >NoWait of just the file name and the Enter immediately
> followed by
> >RunCommand LinkTables. Of course, this would be much
> easier to do in VBA -
> >execute a TransferText with a variable file name.
> >
> >--
> >John Viescas, author
> >"Microsoft Office Access 2003 Inside Out"
> >"Running Microsoft Access 2000"
> >"SQL Queries for Mere Mortals"
> >http://www.viescas.com/
> >(Microsoft Access MVP since 1993)
> >"John Alexander" <(E-Mail Removed)> wrote in
> message
> >news:12d9501c41669$b8df36f0$(E-Mail Removed)...
> >> Hello,
> >> I am linking to many tab delimted text files, which are
> >> not always consistent (i.e.-sometimes columns will be
> >> missing altogether upon refresh, causing problems with
> >> the linked data).
> >>
> >> I may have found a solution by using the "send keys"
> >> action Macro. This seems to work perfect all the way up
> >> to the very end, when the final confirmation
> >> message "finished linking table..." appears. I have
> >> tried everything I can think of to either eliminate the
> >> pop-up (set warnings on to "No") and hardcoding the
> final
> >> {enter} or ~ to emulate clicking "OK". My
> understanding
> >> is that the final message halts the macro, so a
> >> knowledgebase article suggests moving the {enter} or ~
> to
> >> BEFORE the key which causes the confirmation pop-up. I
> >> tried this...still doesn't work. I feel that I am SO
> >> close with this solution. Here are the keys I am
> sending
> >> after setting warnings to "No":
> >>
> >> %fglc:\data\mel\excel stuff for john a\Pitch 35P.txt%
> >> kdnrn%nPitch 35P%fy~
> >>
> >> The final "~" is to simply enter "OK" after the
> >> confirmation, but it wont work.
> >>
> >> Please help...
> >>
> >>
> >
> >
> >.
> >

Thanks so much for your time...
This was the very first method I tried. The problem is
that, without any specification, it doesn't know HOW to
delimit the columns (comma, tab, etc), but WITH the spec,
it becomes too specific to the columns. How can it know
the delimiter unless you tell it?

I ran your method again to be sure, but again, all of the
data is concatenated together in one single column with a
column name that is a long concatenation of all of the
column headings. Are you sure you tried this on a TAB
delimited file? You are getting column separations?
What version of Access? I'm running 2002 with XP.

Maybe you changed the default delimiter to TAB? How?
>-----Original Message-----
>Well, you say the SendKeys you're trying to do is:
>
>%fglc:\data\mel\excel stuff for john a\Pitch 35P.txt%
kdnrn%nPitch 35P%fy~
>
>Let me see if I can figure out what you're attempting to
do...
>
>Alt-F drops down the File menu. G selects Get External
Data, and L selects
>Link Tables - which should open the Link open file
dialog. And then you
>attempt to "type" in the file name, followed by Alt-K
that "clicks" the Link
>button, which opens the text import wizard despite the
fact that you did not
>change the "files of type" box.
>
>The letter D selects Delimited, letter N "clicks" the
Next button on the
>first panel of the Wizard, letter R says the first row
contains column
>names, N clicks Next. Alt-N clicks Next again, and you
try to type in the
>name of the linked table - "Pitch 35P". Alt-F clicks
Finish, and you try to
>close it out with an Enter.
>
>I think what you're trying to do is get the Import
Wizard to ignore the
>specification each time and just link the table "as
is." I tried a little
>test. First, I linked a delimited text file that
contained three columns
>using the Wizard. Sure enough, it created an import
spec with the three
>columns. Next, I removed one of the columns from my
test file. I then
>created a macro to link this file (didn't change the
name of the file or the
>link name) like this:
>
>Action: TransferText
>Transfer Type: Link Delimited
>Specification Name: (left blank!)
>Table Name: MyTable
>File Name: C:\MyFiles\MyTable.txt
>Has Field Names: Yes
>
>Works like a champ without all the Sendkeys folderol.
>
>--
>John Viescas, author
>"Microsoft Office Access 2003 Inside Out"
>"Running Microsoft Access 2000"
>"SQL Queries for Mere Mortals"
>http://www.viescas.com/
>(Microsoft Access MVP since 1993)
>"John" <(E-Mail Removed)> wrote in message
>news:15ba301c41670$8cdd5440$(E-Mail Removed)...
>> Because,
>> as I wrote. All of these txt files could potentially
>> have a different structure (missing columns). I tried
>> your transfer text solution. The problem with that is
it
>> relys on a consistent structure. For example, say I
have
>> a tab delimited file with 3 columns, A, B, and C
>> populated with 1, 2, and 3. When I set this up with a
>> transfer text macro, it needs a "spec" telling it how
to
>> delimit the columns AND (unfortunately necessary) the
>> COLUMN NAMES. I need the column names to be
dynamically
>> read every time, since next time, maybe column B will
be
>> missing on the refreshed file. If column "B" is
missing
>> on the refresh, then the transfer text solution (based
on
>> the given spec of A, B, C) will produce a table A, B, C
>> populated with 1, 3, null (i.e.-the data shifts over a
>> column). Does this make sense?
>>
>> So, to answer your other question, it is not really the
>> filenames that need to be dynamic (there could be 50 or
>> so, but they can, and should be hardcoded), it is
really
>> the STRUCTURE that needs to be dynamic. Actually, I am
>> quite surprised that many people do not have the same
>> problem. I have seen PAGES of code which try to to
that
>> same thing, but I'm not a VBA expert, and I honestly
>> think my send keys solution is perfect except for that
>> last ~, which is supposed to close the idiotic
>> confirmation. Make sense? Or, am I missing something?
>>
>> Thanks!
>>
>> >-----Original Message-----
>> >Why in the WORLD are you using SendKeys? Wouldn't a
>> simple TransferText
>> >command do? If you need to dynamically supply the
file
>> name, do a SendKeys
>> >NoWait of just the file name and the Enter immediately
>> followed by
>> >RunCommand LinkTables. Of course, this would be much
>> easier to do in VBA -
>> >execute a TransferText with a variable file name.
>> >
>> >--
>> >John Viescas, author
>> >"Microsoft Office Access 2003 Inside Out"
>> >"Running Microsoft Access 2000"
>> >"SQL Queries for Mere Mortals"
>> >http://www.viescas.com/
>> >(Microsoft Access MVP since 1993)
>> >"John Alexander" <(E-Mail Removed)> wrote in
>> message
>> >news:12d9501c41669$b8df36f0$(E-Mail Removed)...
>> >> Hello,
>> >> I am linking to many tab delimted text files, which
are
>> >> not always consistent (i.e.-sometimes columns will
be
>> >> missing altogether upon refresh, causing problems
with
>> >> the linked data).
>> >>
>> >> I may have found a solution by using the "send keys"
>> >> action Macro. This seems to work perfect all the
way up
>> >> to the very end, when the final confirmation
>> >> message "finished linking table..." appears. I have
>> >> tried everything I can think of to either eliminate
the
>> >> pop-up (set warnings on to "No") and hardcoding the
>> final
>> >> {enter} or ~ to emulate clicking "OK". My
>> understanding
>> >> is that the final message halts the macro, so a
>> >> knowledgebase article suggests moving the {enter}
or ~
>> to
>> >> BEFORE the key which causes the confirmation pop-
up. I
>> >> tried this...still doesn't work. I feel that I am
SO
>> >> close with this solution. Here are the keys I am
>> sending
>> >> after setting warnings to "No":
>> >>
>> >> %fglc:\data\mel\excel stuff for john a\Pitch
35P.txt%
>> >> kdnrn%nPitch 35P%fy~
>> >>
>> >> The final "~" is to simply enter "OK" after the
>> >> confirmation, but it wont work.
>> >>
>> >> Please help...
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>
>.
>

I just tried this using a COMMA delimited txt file and it
DID work as you suggest, however, it still does not work
on my TAB delimited files. Can you confirm whether your
test was using TAB or comma delimited files? Maybe I can
change the default somehow to default to tab?

Thanks!


>-----Original Message-----
>Well, you say the SendKeys you're trying to do is:
>
>%fglc:\data\mel\excel stuff for john a\Pitch 35P.txt%
kdnrn%nPitch 35P%fy~
>
>Let me see if I can figure out what you're attempting to
do...
>
>Alt-F drops down the File menu. G selects Get External
Data, and L selects
>Link Tables - which should open the Link open file
dialog. And then you
>attempt to "type" in the file name, followed by Alt-K
that "clicks" the Link
>button, which opens the text import wizard despite the
fact that you did not
>change the "files of type" box.
>
>The letter D selects Delimited, letter N "clicks" the
Next button on the
>first panel of the Wizard, letter R says the first row
contains column
>names, N clicks Next. Alt-N clicks Next again, and you
try to type in the
>name of the linked table - "Pitch 35P". Alt-F clicks
Finish, and you try to
>close it out with an Enter.
>
>I think what you're trying to do is get the Import
Wizard to ignore the
>specification each time and just link the table "as
is." I tried a little
>test. First, I linked a delimited text file that
contained three columns
>using the Wizard. Sure enough, it created an import
spec with the three
>columns. Next, I removed one of the columns from my
test file. I then
>created a macro to link this file (didn't change the
name of the file or the
>link name) like this:
>
>Action: TransferText
>Transfer Type: Link Delimited
>Specification Name: (left blank!)
>Table Name: MyTable
>File Name: C:\MyFiles\MyTable.txt
>Has Field Names: Yes
>
>Works like a champ without all the Sendkeys folderol.
>
>--
>John Viescas, author
>"Microsoft Office Access 2003 Inside Out"
>"Running Microsoft Access 2000"
>"SQL Queries for Mere Mortals"
>http://www.viescas.com/
>(Microsoft Access MVP since 1993)
>"John" <(E-Mail Removed)> wrote in message
>news:15ba301c41670$8cdd5440$(E-Mail Removed)...
>> Because,
>> as I wrote. All of these txt files could potentially
>> have a different structure (missing columns). I tried
>> your transfer text solution. The problem with that is
it
>> relys on a consistent structure. For example, say I
have
>> a tab delimited file with 3 columns, A, B, and C
>> populated with 1, 2, and 3. When I set this up with a
>> transfer text macro, it needs a "spec" telling it how
to
>> delimit the columns AND (unfortunately necessary) the
>> COLUMN NAMES. I need the column names to be
dynamically
>> read every time, since next time, maybe column B will
be
>> missing on the refreshed file. If column "B" is
missing
>> on the refresh, then the transfer text solution (based
on
>> the given spec of A, B, C) will produce a table A, B, C
>> populated with 1, 3, null (i.e.-the data shifts over a
>> column). Does this make sense?
>>
>> So, to answer your other question, it is not really the
>> filenames that need to be dynamic (there could be 50 or
>> so, but they can, and should be hardcoded), it is
really
>> the STRUCTURE that needs to be dynamic. Actually, I am
>> quite surprised that many people do not have the same
>> problem. I have seen PAGES of code which try to to
that
>> same thing, but I'm not a VBA expert, and I honestly
>> think my send keys solution is perfect except for that
>> last ~, which is supposed to close the idiotic
>> confirmation. Make sense? Or, am I missing something?
>>
>> Thanks!
>>
>> >-----Original Message-----
>> >Why in the WORLD are you using SendKeys? Wouldn't a
>> simple TransferText
>> >command do? If you need to dynamically supply the
file
>> name, do a SendKeys
>> >NoWait of just the file name and the Enter immediately
>> followed by
>> >RunCommand LinkTables. Of course, this would be much
>> easier to do in VBA -
>> >execute a TransferText with a variable file name.
>> >
>> >--
>> >John Viescas, author
>> >"Microsoft Office Access 2003 Inside Out"
>> >"Running Microsoft Access 2000"
>> >"SQL Queries for Mere Mortals"
>> >http://www.viescas.com/
>> >(Microsoft Access MVP since 1993)
>> >"John Alexander" <(E-Mail Removed)> wrote in
>> message
>> >news:12d9501c41669$b8df36f0$(E-Mail Removed)...
>> >> Hello,
>> >> I am linking to many tab delimted text files, which
are
>> >> not always consistent (i.e.-sometimes columns will
be
>> >> missing altogether upon refresh, causing problems
with
>> >> the linked data).
>> >>
>> >> I may have found a solution by using the "send keys"
>> >> action Macro. This seems to work perfect all the
way up
>> >> to the very end, when the final confirmation
>> >> message "finished linking table..." appears. I have
>> >> tried everything I can think of to either eliminate
the
>> >> pop-up (set warnings on to "No") and hardcoding the
>> final
>> >> {enter} or ~ to emulate clicking "OK". My
>> understanding
>> >> is that the final message halts the macro, so a
>> >> knowledgebase article suggests moving the {enter}
or ~
>> to
>> >> BEFORE the key which causes the confirmation pop-
up. I
>> >> tried this...still doesn't work. I feel that I am
SO
>> >> close with this solution. Here are the keys I am
>> sending
>> >> after setting warnings to "No":
>> >>
>> >> %fglc:\data\mel\excel stuff for john a\Pitch
35P.txt%
>> >> kdnrn%nPitch 35P%fy~
>> >>
>> >> The final "~" is to simply enter "OK" after the
>> >> confirmation, but it wont work.
>> >>
>> >> Please help...
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>
>.
>

Yup, it works with commas (the default delimiter) but not tabs.

One solution would be to read through the text file in code and replace tabs
with commas.

Dim strData As String

' Open the input text file
Open "c:\data\mel\excel stuff for john a\Pitch 35P.txt" For Input As #1
' Open the output text file
Open "c:\data\mel\excel stuff for john a\Pitch 35Pcomma.txt" For Output
As #2
' Get the first record
Input #1, strData
' Loop until hit end of file
Do While Not EOF(1)
' Replace all tabs with commas
strData = Replace(strData, vbTab, ",")
' Write the changed data - using Print to avoid parsing
Print #2, strData
' Get the next input
Input #1, strData
Loop
' Close both files
Close #1
Close #2

' Now link the "fixed" file
DoCmd.TransferText acLinkDelim, , "Pitch 35P", _
"c:\data\mel\excel stuff for john a\Pitch 35Pcomma.txt", True



--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
"john" <(E-Mail Removed)> wrote in message
news:1614901c41683$9f331b30$(E-Mail Removed)...
> I just tried this using a COMMA delimited txt file and it
> DID work as you suggest, however, it still does not work
> on my TAB delimited files. Can you confirm whether your
> test was using TAB or comma delimited files? Maybe I can
> change the default somehow to default to tab?
>
> Thanks!
>
>
> >-----Original Message-----
> >Well, you say the SendKeys you're trying to do is:
> >
> >%fglc:\data\mel\excel stuff for john a\Pitch 35P.txt%
> kdnrn%nPitch 35P%fy~
> >
> >Let me see if I can figure out what you're attempting to
> do...
> >
> >Alt-F drops down the File menu. G selects Get External
> Data, and L selects
> >Link Tables - which should open the Link open file
> dialog. And then you
> >attempt to "type" in the file name, followed by Alt-K
> that "clicks" the Link
> >button, which opens the text import wizard despite the
> fact that you did not
> >change the "files of type" box.
> >
> >The letter D selects Delimited, letter N "clicks" the
> Next button on the
> >first panel of the Wizard, letter R says the first row
> contains column
> >names, N clicks Next. Alt-N clicks Next again, and you
> try to type in the
> >name of the linked table - "Pitch 35P". Alt-F clicks
> Finish, and you try to
> >close it out with an Enter.
> >
> >I think what you're trying to do is get the Import
> Wizard to ignore the
> >specification each time and just link the table "as
> is." I tried a little
> >test. First, I linked a delimited text file that
> contained three columns
> >using the Wizard. Sure enough, it created an import
> spec with the three
> >columns. Next, I removed one of the columns from my
> test file. I then
> >created a macro to link this file (didn't change the
> name of the file or the
> >link name) like this:
> >
> >Action: TransferText
> >Transfer Type: Link Delimited
> >Specification Name: (left blank!)
> >Table Name: MyTable
> >File Name: C:\MyFiles\MyTable.txt
> >Has Field Names: Yes
> >
> >Works like a champ without all the Sendkeys folderol.
> >
> >--
> >John Viescas, author
> >"Microsoft Office Access 2003 Inside Out"
> >"Running Microsoft Access 2000"
> >"SQL Queries for Mere Mortals"
> >http://www.viescas.com/
> >(Microsoft Access MVP since 1993)
> >"John" <(E-Mail Removed)> wrote in message
> >news:15ba301c41670$8cdd5440$(E-Mail Removed)...
> >> Because,
> >> as I wrote. All of these txt files could potentially
> >> have a different structure (missing columns). I tried
> >> your transfer text solution. The problem with that is
> it
> >> relys on a consistent structure. For example, say I
> have
> >> a tab delimited file with 3 columns, A, B, and C
> >> populated with 1, 2, and 3. When I set this up with a
> >> transfer text macro, it needs a "spec" telling it how
> to
> >> delimit the columns AND (unfortunately necessary) the
> >> COLUMN NAMES. I need the column names to be
> dynamically
> >> read every time, since next time, maybe column B will
> be
> >> missing on the refreshed file. If column "B" is
> missing
> >> on the refresh, then the transfer text solution (based
> on
> >> the given spec of A, B, C) will produce a table A, B, C
> >> populated with 1, 3, null (i.e.-the data shifts over a
> >> column). Does this make sense?
> >>
> >> So, to answer your other question, it is not really the
> >> filenames that need to be dynamic (there could be 50 or
> >> so, but they can, and should be hardcoded), it is
> really
> >> the STRUCTURE that needs to be dynamic. Actually, I am
> >> quite surprised that many people do not have the same
> >> problem. I have seen PAGES of code which try to to
> that
> >> same thing, but I'm not a VBA expert, and I honestly
> >> think my send keys solution is perfect except for that
> >> last ~, which is supposed to close the idiotic
> >> confirmation. Make sense? Or, am I missing something?
> >>
> >> Thanks!
> >>
> >> >-----Original Message-----
> >> >Why in the WORLD are you using SendKeys? Wouldn't a
> >> simple TransferText
> >> >command do? If you need to dynamically supply the
> file
> >> name, do a SendKeys
> >> >NoWait of just the file name and the Enter immediately
> >> followed by
> >> >RunCommand LinkTables. Of course, this would be much
> >> easier to do in VBA -
> >> >execute a TransferText with a variable file name.
> >> >
> >> >--
> >> >John Viescas, author
> >> >"Microsoft Office Access 2003 Inside Out"
> >> >"Running Microsoft Access 2000"
> >> >"SQL Queries for Mere Mortals"
> >> >http://www.viescas.com/
> >> >(Microsoft Access MVP since 1993)
> >> >"John Alexander" <(E-Mail Removed)> wrote in
> >> message
> >> >news:12d9501c41669$b8df36f0$(E-Mail Removed)...
> >> >> Hello,
> >> >> I am linking to many tab delimted text files, which
> are
> >> >> not always consistent (i.e.-sometimes columns will
> be
> >> >> missing altogether upon refresh, causing problems
> with
> >> >> the linked data).
> >> >>
> >> >> I may have found a solution by using the "send keys"
> >> >> action Macro. This seems to work perfect all the
> way up
> >> >> to the very end, when the final confirmation
> >> >> message "finished linking table..." appears. I have
> >> >> tried everything I can think of to either eliminate
> the
> >> >> pop-up (set warnings on to "No") and hardcoding the
> >> final
> >> >> {enter} or ~ to emulate clicking "OK". My
> >> understanding
> >> >> is that the final message halts the macro, so a
> >> >> knowledgebase article suggests moving the {enter}
> or ~
> >> to
> >> >> BEFORE the key which causes the confirmation pop-
> up. I
> >> >> tried this...still doesn't work. I feel that I am
> SO
> >> >> close with this solution. Here are the keys I am
> >> sending
> >> >> after setting warnings to "No":
> >> >>
> >> >> %fglc:\data\mel\excel stuff for john a\Pitch
> 35P.txt%
> >> >> kdnrn%nPitch 35P%fy~
> >> >>
> >> >> The final "~" is to simply enter "OK" after the
> >> >> confirmation, but it wont work.
> >> >>
> >> >> Please help...
> >> >>
> >> >>
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >