TransferSpreadsheet

  • Thread starter Thread starter kid
  • Start date Start date
K

kid

Is there a way to use the TransferSpreadsheet
action (in a macro) but rather than giving it the address
it asking the user at run time where the file is?

Kid
 
In the destination Object Name argument in your macro
design, put...
=InputBox("What is the name of the table?")

Jim
 
Soapbox on
Macros are a legacy left over from Version 1.0.
They should never be used, except in VERY limited circumstances.
They are very limited , and can cause all sorts of problems
Move the TransferSpreadsheet macro to code
The syntax is
DoCmd.TransferSpreadsheet [transfertype][, spreadsheettype],
tablename, filename[, hasfieldnames][, range]

For all details look up the help file

Your code will ned up looking like...
Sub GetSpreadsheet ()
dim strFileName as string
strString = NZ(InputBox, "Enter a fileName")

Docmd.TransferSpreadSheet AcImport, acSpreadsheetTypeExcel8, _
"tblDestination", strFileName

end Sub

You can get very sophisticated with your input box. It is even
possibly to use the Windows FileOpen Dialog box to choose it
You can also alter the Spreadsheet type dynamically,(or progressively
until one works!), but I leave these wrinkles as an exercise for the
reader. :)>

HTH
Regards Greg Kraushaar
Wentworth Falls Australia
(Do not email - the reply address is a Spam spoofer)
(If you really must, remove all UCase and numbers)

Move it to Code
 
I understand the syntax for transfering data from excel to Access or
the other way around:
DoCmd.TransferSpreadsheet [transfertype][, spreadsheettype],
tablename, filename[, hasfieldnames][, range]

I have problem with [ range]. I've tried this:

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel97,
"TblTmpDept", "November.xls", True, "Dept_Entry!A1: S75"

I got an error message:
Run-Time error '3011'
The Microsoft Jet database engine could not find the object
"'Dept_Entry$A1: S75". Make sure the object exists....

Any help would be appreciated, Many thanks
 
Back
Top