Importing from a closed Workbook

[Alan]

Sorry to re-post this but its driving me up the wall !!

Not sure why this happens

I have an excel work book with multiple sheets.
The Workbook has several 'dated' sheets ie 25-06-09 /26-06-09 / etc
Not my naming convention but one provided !

I only want to import the current day and have used the TransferSpreadsheet
method below.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TEST",
"C:\Test", True, "26-06-09$"

This works fine if the Workbook is open, however if closed it only creates
the first column and no data ??

Is there something I am missing - I have several similar worksheets to
import and trying to avoid opening and closing the 12 workbooks to allow
import to access

 

[Alan]

Jim

Thanks for your reply and I have tried your suggestions
My code simply provides for a repetion of the code to import from multiple
workbooks

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TEST",
"C:\Test.xls", True, "26-06-09$"

I read in another post that th $ was to allow all cells in a particular
worksheet in a work book to be copied.
I have also tried with the .xls extention however this again yeilds the same
results

It is very puzzling as likewise I have imported from may closed workbooks,
and csvs without issue, however this is the first time I have tried to select
a specific worksheet !!

Regards
Many Thanks

Alan

 

[Ken Snell [MVP]]

The $ at the end of the Range argument's string value tells ACCESS that
you're importing a Range that is named what is to the left of the $
character. Worksheet names are added to the Range collection in the EXCEL
file, so when you reference a single worksheet as the source of data for the
Range argument, putting the $ at the end is a good thing to do.

Because of the presence of hyphen characters in the worksheet name, I'd try
this syntax as well to see if it works better:
"'26-06-09'$"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I don't know about the $ at the end, never seen that before - try this
instead just for kicks:

26-06-09!A1:IV65536

Pretty sure this tells it to import the entire worksheet. It still makes
no
sense to me that you would have a problem if the worksbook is closed.
Maybe
the $ is causing the problem?

Jim

Thanks for your reply and I have tried your suggestions
My code simply provides for a repetion of the code to import from multiple
workbooks

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TEST",
"C:\Test.xls", True, "26-06-09$"

I read in another post that th $ was to allow all cells in a particular
worksheet in a work book to be copied.
I have also tried with the .xls extention however this again yeilds the
same
results

It is very puzzling as likewise I have imported from may closed workbooks,
and csvs without issue, however this is the first time I have tried to
select
a specific worksheet !!

Regards
Many Thanks

Alan

I remember this post, and I'm not sure the answers will be different,
but I
guess it's worth a shot. First off, if anything, you would potentially
have a

[quoted text clipped - 38 lines]

import and trying to avoid opening and closing the 12 workbooks to
allow
import to access

 

[Alan]

Jim / Ken

Many Thanks for your time - but still no joy

Jim -
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TEST",
"C:\Test.xls", True, "26-06-09!A1:IV65536"

Your option gives me the Run-time error '3011' The microsoft jet database
engine could not find the object '26-06-09$A1:IV65536'. etc

Ken -
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TEST",
"C:\Test.xls", True, "'26-06-09'$"

your suggestion now presents me with Run-time Error '3125'
''26-06-09'$' is not a valid name. etc

???????????????
Really Confused
--
Kind Regards and
Many Thanks

Alan

The $ at the end of the Range argument's string value tells ACCESS that
you're importing a Range that is named what is to the left of the $
character. Worksheet names are added to the Range collection in the EXCEL
file, so when you reference a single worksheet as the source of data for the
Range argument, putting the $ at the end is a good thing to do.

Because of the presence of hyphen characters in the worksheet name, I'd try
this syntax as well to see if it works better:
"'26-06-09'$"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I don't know about the $ at the end, never seen that before - try this
instead just for kicks:

26-06-09!A1:IV65536

Pretty sure this tells it to import the entire worksheet. It still makes
no
sense to me that you would have a problem if the worksbook is closed.
Maybe
the $ is causing the problem?

Jim

Thanks for your reply and I have tried your suggestions
My code simply provides for a repetion of the code to import from multiple
workbooks

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TEST",
"C:\Test.xls", True, "26-06-09$"

I read in another post that th $ was to allow all cells in a particular
worksheet in a work book to be copied.
I have also tried with the .xls extention however this again yeilds the
same
results

It is very puzzling as likewise I have imported from may closed workbooks,
and csvs without issue, however this is the first time I have tried to
select
a specific worksheet !!

Regards
Many Thanks

Alan

I remember this post, and I'm not sure the answers will be different,
but I
guess it's worth a shot. First off, if anything, you would potentially
have a
[quoted text clipped - 38 lines]
import and trying to avoid opening and closing the 12 workbooks to
allow
import to access

 

[Ken Snell [MVP]]

Is the EXCEL workbook file password-protected?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Jim / Ken

Many Thanks for your time - but still no joy

Jim -
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TEST",
"C:\Test.xls", True, "26-06-09!A1:IV65536"

Your option gives me the Run-time error '3011' The microsoft jet database
engine could not find the object '26-06-09$A1:IV65536'. etc

Ken -
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TEST",
"C:\Test.xls", True, "'26-06-09'$"

your suggestion now presents me with Run-time Error '3125'
''26-06-09'$' is not a valid name. etc

???????????????
Really Confused
--
Kind Regards and
Many Thanks

Alan

The $ at the end of the Range argument's string value tells ACCESS that
you're importing a Range that is named what is to the left of the $
character. Worksheet names are added to the Range collection in the EXCEL
file, so when you reference a single worksheet as the source of data for
the
Range argument, putting the $ at the end is a good thing to do.

Because of the presence of hyphen characters in the worksheet name, I'd
try
this syntax as well to see if it works better:
"'26-06-09'$"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I don't know about the $ at the end, never seen that before - try this
instead just for kicks:

26-06-09!A1:IV65536

Pretty sure this tells it to import the entire worksheet. It still
makes
no
sense to me that you would have a problem if the worksbook is closed.
Maybe
the $ is causing the problem?

Alan wrote:
Jim

Thanks for your reply and I have tried your suggestions
My code simply provides for a repetion of the code to import from
multiple
workbooks

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TEST",
"C:\Test.xls", True, "26-06-09$"

I read in another post that th $ was to allow all cells in a particular
worksheet in a work book to be copied.
I have also tried with the .xls extention however this again yeilds the
same
results

It is very puzzling as likewise I have imported from may closed
workbooks,
and csvs without issue, however this is the first time I have tried to
select
a specific worksheet !!

Regards
Many Thanks

Alan

I remember this post, and I'm not sure the answers will be different,
but I
guess it's worth a shot. First off, if anything, you would
potentially
have a
[quoted text clipped - 38 lines]
import and trying to avoid opening and closing the 12 workbooks to
allow
import to access

 

[Alan]

Ken No the workbook is not protected - I am kinda stumped and know in my head
I will have done something stupid !!

Thanks again for your time
Regards

Alan

Is the EXCEL workbook file password-protected?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Jim / Ken

Many Thanks for your time - but still no joy

Jim -
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TEST",
"C:\Test.xls", True, "26-06-09!A1:IV65536"

Your option gives me the Run-time error '3011' The microsoft jet database
engine could not find the object '26-06-09$A1:IV65536'. etc

Ken -
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TEST",
"C:\Test.xls", True, "'26-06-09'$"

your suggestion now presents me with Run-time Error '3125'
''26-06-09'$' is not a valid name. etc

???????????????
Really Confused
--
Kind Regards and
Many Thanks

Alan

The $ at the end of the Range argument's string value tells ACCESS that
you're importing a Range that is named what is to the left of the $
character. Worksheet names are added to the Range collection in the EXCEL
file, so when you reference a single worksheet as the source of data for
the
Range argument, putting the $ at the end is a good thing to do.

Because of the presence of hyphen characters in the worksheet name, I'd
try
this syntax as well to see if it works better:
"'26-06-09'$"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I don't know about the $ at the end, never seen that before - try this
instead just for kicks:

26-06-09!A1:IV65536

Pretty sure this tells it to import the entire worksheet. It still
makes
no
sense to me that you would have a problem if the worksbook is closed.
Maybe
the $ is causing the problem?

Alan wrote:
Jim

Thanks for your reply and I have tried your suggestions
My code simply provides for a repetion of the code to import from
multiple
workbooks

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TEST",
"C:\Test.xls", True, "26-06-09$"

I read in another post that th $ was to allow all cells in a particular
worksheet in a work book to be copied.
I have also tried with the .xls extention however this again yeilds the
same
results

It is very puzzling as likewise I have imported from may closed
workbooks,
and csvs without issue, however this is the first time I have tried to
select
a specific worksheet !!

Regards
Many Thanks

Alan

I remember this post, and I'm not sure the answers will be different,
but I
guess it's worth a shot. First off, if anything, you would
potentially
have a
[quoted text clipped - 38 lines]
import and trying to avoid opening and closing the 12 workbooks to
allow
import to access

 

[Alan]

Jim

my code has been stripped to try and sort this and literally contains the
sincele transfer event

Public Sub Importxls

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TEST",
"C:\Test.xls", True, "'26-06-09'!"

End Sub

Sorry Yeah I tried it according to the web page and get the same error
although I note the error is returning with the $ character rather than the !
character in the code ??

Run-time Error '3125'
''26-06-09'$' is not a valid name. etc

??????
Dont know if it makes a difference but using office 2003
--
Many Thanks

Alan

Did you try what I suggested just above? From what I could see, you hadn't
tried that yet. From what that website shows you should have a ! at the end
if you want the whole sheet and not a $ (not saying that's definite, but it's
what that site shows). Also, you might want to show all the code in the
routine if you're still having problems - you're only showing us the
TransferSpreadhseet command. There may be something else in your code that
you think is OK that is causing the problem.

Ken No the workbook is not protected - I am kinda stumped and know in my head
I will have done something stupid !!

Thanks again for your time
Regards

Alan

Is the EXCEL workbook file password-protected?

[quoted text clipped - 79 lines]

allow
import to access

 

[Ken Snell [MVP]]

Does the worksheet contain formulas to provide the data for the cells that
you're trying to read/import?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Jim

my code has been stripped to try and sort this and literally contains the
sincele transfer event

Public Sub Importxls

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TEST",
"C:\Test.xls", True, "'26-06-09'!"

End Sub

Sorry Yeah I tried it according to the web page and get the same error
although I note the error is returning with the $ character rather than
the !
character in the code ??

Run-time Error '3125'
''26-06-09'$' is not a valid name. etc

??????
Dont know if it makes a difference but using office 2003
--
Many Thanks

Alan

Did you try what I suggested just above? From what I could see, you
hadn't
tried that yet. From what that website shows you should have a ! at the
end
if you want the whole sheet and not a $ (not saying that's definite, but
it's
what that site shows). Also, you might want to show all the code in the
routine if you're still having problems - you're only showing us the
TransferSpreadhseet command. There may be something else in your code
that
you think is OK that is causing the problem.

Ken No the workbook is not protected - I am kinda stumped and know in my
head
I will have done something stupid !!

Thanks again for your time
Regards

Alan

Is the EXCEL workbook file password-protected?

[quoted text clipped - 79 lines]
allow
import to access

 

[Douglas J. Steele]

I believe the dollar sign needs to be inside the single quotes:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TEST",
"C:\Test.xls", True, "'26-06-09$'!"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Jim

my code has been stripped to try and sort this and literally contains the
sincele transfer event

Public Sub Importxls

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TEST",
"C:\Test.xls", True, "'26-06-09'!"

End Sub

Sorry Yeah I tried it according to the web page and get the same error
although I note the error is returning with the $ character rather than
the !
character in the code ??

Run-time Error '3125'
''26-06-09'$' is not a valid name. etc

??????
Dont know if it makes a difference but using office 2003
--
Many Thanks

Alan

Did you try what I suggested just above? From what I could see, you
hadn't
tried that yet. From what that website shows you should have a ! at the
end
if you want the whole sheet and not a $ (not saying that's definite, but
it's
what that site shows). Also, you might want to show all the code in the
routine if you're still having problems - you're only showing us the
TransferSpreadhseet command. There may be something else in your code
that
you think is OK that is causing the problem.

Ken No the workbook is not protected - I am kinda stumped and know in my
head
I will have done something stupid !!

Thanks again for your time
Regards

Alan

Is the EXCEL workbook file password-protected?

[quoted text clipped - 79 lines]
allow
import to access

 

[Alan]

Sorry Guys
Seems Im taking up much of your time with this and its likely to be operator
error somewhere

Ken...

No formulas in the Workbook

Douglas...

Getting similar error as before

Run-time Error '3125'
''26-06-09$'$' is not a valid name. etc

Jim

I have tried this again but get the same result. If I dont have a table it
creates one with only a column reference to the first field in the worksheet.
If I do have a table to import into is appears to import nothing

???????????????????????????

I do appreciate all your time
Many Thanks again

Regards

Alan

I believe the dollar sign needs to be inside the single quotes:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TEST",
"C:\Test.xls", True, "'26-06-09$'!"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Jim

my code has been stripped to try and sort this and literally contains the
sincele transfer event

Public Sub Importxls

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TEST",
"C:\Test.xls", True, "'26-06-09'!"

End Sub

Sorry Yeah I tried it according to the web page and get the same error
although I note the error is returning with the $ character rather than
the !
character in the code ??

Run-time Error '3125'
''26-06-09'$' is not a valid name. etc

??????
Dont know if it makes a difference but using office 2003
--
Many Thanks

Alan

Did you try what I suggested just above? From what I could see, you
hadn't
tried that yet. From what that website shows you should have a ! at the
end
if you want the whole sheet and not a $ (not saying that's definite, but
it's
what that site shows). Also, you might want to show all the code in the
routine if you're still having problems - you're only showing us the
TransferSpreadhseet command. There may be something else in your code
that
you think is OK that is causing the problem.


Alan wrote:
Ken No the workbook is not protected - I am kinda stumped and know in my
head
I will have done something stupid !!

Thanks again for your time
Regards

Alan

Is the EXCEL workbook file password-protected?

[quoted text clipped - 79 lines]
allow
import to access