send keys macro solution to linking many txt files please...

  • Thread starter Thread starter John Alexander
  • Start date Start date
J

John Alexander

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)
 
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!
 
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)
 
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?
 
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!
 
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)
 
Back
Top