Exporting to Excel and importing back (Access2007) - Determing Ran

  • Thread starter Thread starter JiiPee
  • Start date Start date
J

JiiPee

Hi

I'm collecting and updating information on customers in an Access 2007
database continiously via Excel. For this purpose I first import the data
from Access to Excel sheet "Input" via Access oledb link. This works fine.

When I get back the files I use the transferspreadsheet command. So far I
have had a separate sheet "Output" where I have all the data linked with a
formula. Again this has worked fine. I would like to get rid of this and keep
the data on the "Input" sheet only and let the people to make changes in that
sheet.

I've tested that Excel recognizes a range called: "Table_database.accdb"
where 'database' is the name of my database and the range covers exactly the
fields in the Excel import from Access. In addition, I manually imported the
file into access and the wizard automatically proposed a Range called
"database#accdb". So it seems that Access can identify the correct range
name. However, when I try to use any of the above as a 'Range' on
transferspreadhsheet command, it fails.

If I change the imported table to a range in Excel and name it as "Range",
the transferspreadhseet again recognizes the correct range. I dont want to do
this as I woudl prefer to use the same file over and over again.

Would anyone have a solution to this?
Jukka
 
Try enclosing the range value with ' and ending it with ! in the Range
argument of the TransferSpreadsheet action:

"'Table_database.accdb'!"
 
Thanks Ken

I did some testing and it showed to be enough that I changed the # to .
(dot) in the name of the range, which was shown in the import wizard. In
practise:
From: database#accdb
To: database.accdb

Now the transferspreadsheet imports the correct data back to Access even if
the number of rows change in the query that is first exported to Excel.
 
Back
Top