Importing Excel worksheets

[Guest]

Thanks you for any help you can give

I am importing data from several worksheets into an existing table
The worksheet names have spaces in them so I am putting single quotes around the sheet name in the Docmd.Transferspreadsheet command. However, I am also giving a specific range of cells to import and I get the error message that it could not be found

My line of code looks like this

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "TableName", XLS_Name, -1,
"'" & curSheetName & "'!A1:AF27520

Do I need to put single quotes around the range of cells or around the whole string or what

Thanks.

 

[John Nurick]

Hi JP,

I never seem able to get this to work the way I want. Instead, I create
named ranges in the Excel workbook that cover the ranges of cells I want
to import, and then specify these when importing. This works smoothly.

 

[Guest]

What would the syntax for that be

Thanks

----- John Nurick wrote: ----

Hi JP

I never seem able to get this to work the way I want. Instead, I creat
named ranges in the Excel workbook that cover the ranges of cells I wan
to import, and then specify these when importing. This works smoothly

On Tue, 6 Apr 2004 09:36:07 -0700, JP in Denve

Thanks you for any help you can give
The worksheet names have spaces in them so I am putting single quotes around the sheet name in the Docmd.Transferspreadsheet command. However, I am also giving a specific range of cells to import and I get the error message that it could not be found
"'" & curSheetName & "'!A1:AF27520

-
John Nurick [Microsoft Access MVP

Please respond in the newgroup and not by email

 

[John Nurick]

Since your code has access to the sheet name and the range of cells
(e.g. "A1:AF27520") I'll assume that it's previously opened an Excel
Workbook object. In that case you just do something like this:

oWorkbook.Names.Add Name:="MyName", RefersTo:="='" _
& curSheetName & "'!$B$5:$R$17"

Note that if you use an "A1-style" reference it must be an absolute one;
altenatively you can use an R1C1 reference like this:
... RefersToR1C1:="='Sheet1'!R3C1:R9C5"

If you're currently learning the ranges you need to import by actually
launching Excel and examining the workbook, create the names then
(Insert|Name|Define).

 

[Guest]

Thanks John

The workbook I am importing is actually maintained by another company working on the same project we are so I am not inclined to make any kind of edits to it. The solution I came up with is to make a copy of the workbook, work with it and then delete it. At this point, I am just removing the spaces from the worksheet names but your suggestion is a good one too. I'll remenber it for future reference

----- John Nurick wrote: ----

Since your code has access to the sheet name and the range of cell
(e.g. "A1:AF27520") I'll assume that it's previously opened an Exce
Workbook object. In that case you just do something like this

oWorkbook.Names.Add Name:="MyName", RefersTo:="='"
& curSheetName & "'!$B$5:$R$17

Note that if you use an "A1-style" reference it must be an absolute one
altenatively you can use an R1C1 reference like this:
... RefersToR1C1:="='Sheet1'!R3C1:R9C5"

If you're currently learning the ranges you need to import by actuall
launching Excel and examining the workbook, create the names the
(Insert|Name|Define)

On Tue, 6 Apr 2004 14:11:04 -0700, JP in Denve

What would the syntax for that be
Thanks
----- John Nurick wrote: ----
Hi JP
I never seem able to get this to work the way I want. Instead, I creat

named ranges in the Excel workbook that cover the ranges of cells I wan
to import, and then specify these when importing. This works smoothly

The worksheet names have spaces in them so I am putting single quotes around the sheet name in the Docmd.Transferspreadsheet command. However, I am also giving a specific range of cells to import and I get the error message that it could not be found
"'" & curSheetName & "'!A1:AF27520
-

John Nurick [Microsoft Access MVP

Please respond in the newgroup and not by email

-
John Nurick [Microsoft Access MVP

Please respond in the newgroup and not by email