Sendkeys not working in Access 2007

[Sarah]

I have a database I created in access 2003 that I open up in 2007. I went to
the trust center and allowed all macros and listed the location of my
database under trusted locations. The macro let's me select the sendkeys
command. This is the list of commands:

SENDKEYS {TAB}
SENDKEYS E
SENDKEYS E
SENDKEYS E
SENDKEYS {ENTER}
RunCommand Import

The purpose is to change the File type to Excel 97-2003 Workbook (*.xls) and
it doesn't do that. It runs the import command and then stops there.

Any suggestions?

Thank you.

Sarah

 

[David W. Fenton]

Any suggestions?

Never, ever use Sendkeys.

 

[Marshall Barton]

I have a database I created in access 2003 that I open up in 2007. I went to
the trust center and allowed all macros and listed the location of my
database under trusted locations. The macro let's me select the sendkeys
command. This is the list of commands:

SENDKEYS {TAB}
SENDKEYS E
SENDKEYS E
SENDKEYS E
SENDKEYS {ENTER}
RunCommand Import

The purpose is to change the File type to Excel 97-2003 Workbook (*.xls) and
it doesn't do that. It runs the import command and then stops there.

Find another way.

Just in case you think you can ignore David's mild
admonition (I would use much stronger words), think instead
about the fact that SendKeys provides no way for you to
specify the object that is supposed to receive the keys you
send. Lacking that, Access has to guess where it should
send the keys and you really should not rely on guesses to
make your program work.

Besides, SendKeys has a bunch of bugs.

 

[Sarah]

How can I make this work without using sendkeys. There is no Import option
which let's you programmtically select the File Type. I don't want the user
to have to select the file type. Do you have any suggestions?

 

[Sarah]

How can I make this work without using sendkeys. There is no Import option
which let's you programmtically select the File Type. I don't want the user
to have to select the file type. Do you have any suggestions?

 

[Sarah]

How can I make this work without using sendkeys. There is no Import option
which let's you programmtically select the File Type. I don't want the user
to have to select the file type. Do you have any suggestions?

 

[David W. Fenton]

How can I make this work without using sendkeys. There is no
Import option which let's you programmtically select the File
Type. I don't want the user to have to select the file type. Do
you have any suggestions?

Use one of the programmatic methods for importing,
DoCmd.TransferDatabase, or DoCmd.TransferSpreadsheet. If you don't
want to write code, use a macro.

 

[Sarah]

That won't work in this instance. Since I prompt the user for a file. The
file name can vary. When the screen pops up for the user to select the file
I want the file type to be set to Excel 97-2003 Workbook. How can I get that
to happen?

 

[David W. Fenton]

That won't work in this instance. Since I prompt the user for a
file. The file name can vary.

Er, how does that matter? I've implemented code dozens of times for
exporting in various formats with a user-defined file name.

When the screen pops up for the user to select the file
I want the file type to be set to Excel 97-2003 Workbook. How can
I get that to happen?

You have to code the file open dialog to get the filename, and
assign it to a variable. Then use that in the
TransferDatabase/TransferSpreadsheet command.

SendKeys is *never* necessary for such operations.

 

[Sarah]

Now I get it after reading your response below as far as assigning the
filename to a variable. Thank you!