hi guys and gals,
i am trying to read in several excel workbooks which seems to be working
however,
one file i have tried to read in i get this

This table contains cells that are outside the range of cells defined in
this spreadsheet. (Error 3673)

i suspect i could define usable cells in the spreadsheet before it is read
in just in case there is a problem. the cells in question are A1:P7 (they do
not always have data in them but my coding alows for that) but i don't know
how to do it.

many thanks in advance

Arnie

Are you trying to do this using Access, the database product that's part of
Office Professional (and the focus of this particular newsgroup)?

If not, I'd recommend reposting your question to a newsgroup related to
Excel.

If so, how are you reading the workbooks? If you're using VBA code, post it.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Arnie" <(E-Mail Removed)> wrote in message
news:2879FE28-9F83-4424-A1FC-(E-Mail Removed)...
> hi guys and gals,
> i am trying to read in several excel workbooks which seems to be working
> however,
> one file i have tried to read in i get this
>
> This table contains cells that are outside the range of cells defined in
> this spreadsheet. (Error 3673)
>
> i suspect i could define usable cells in the spreadsheet before it is read
> in just in case there is a problem. the cells in question are A1:P7 (they
> do
> not always have data in them but my coding alows for that) but i don't
> know
> how to do it.
>
> many thanks in advance
>
> Arnie

Douglas hi
yes i am using Access part of the code i use is
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTemp_V1",
strFile, , "Calls!K2:K2"

the spreadsheet (in this case only has data in it up to J) i am trying to
read is causing the below error i hoped there was a way of defining the cells
perhaps by setting the print area might solve it?

"Douglas J. Steele" wrote:

> Are you trying to do this using Access, the database product that's part of
> Office Professional (and the focus of this particular newsgroup)?
>
> If not, I'd recommend reposting your question to a newsgroup related to
> Excel.
>
> If so, how are you reading the workbooks? If you're using VBA code, post it.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Arnie" <(E-Mail Removed)> wrote in message
> news:2879FE28-9F83-4424-A1FC-(E-Mail Removed)...
> > hi guys and gals,
> > i am trying to read in several excel workbooks which seems to be working
> > however,
> > one file i have tried to read in i get this
> >
> > This table contains cells that are outside the range of cells defined in
> > this spreadsheet. (Error 3673)
> >
> > i suspect i could define usable cells in the spreadsheet before it is read
> > in just in case there is a problem. the cells in question are A1:P7 (they
> > do
> > not always have data in them but my coding alows for that) but i don't
> > know
> > how to do it.
> >
> > many thanks in advance
> >
> > Arnie
>
>
>

This is how I do it:
DoCmd.DeleteObject acTable, "tblYourTableName"
DoCmd.TransferSpreadsheet acImport, 8, " tblYourTableName ",
"C:\TempFolder\RestOfPathHere\ExcelFileName", True, "Sheet!A1:J100"

Here is another example:
http://www.xtremevbtalk.com/showthread.php?t=73618

Finally, you could set up the process as a macro, get that working, then
save the macro as a module to see the code. Great learning experience!!

HTH,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Arnie" wrote:

> Douglas hi
> yes i am using Access part of the code i use is
> DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTemp_V1",
> strFile, , "Calls!K2:K2"
>
> the spreadsheet (in this case only has data in it up to J) i am trying to
> read is causing the below error i hoped there was a way of defining the cells
> perhaps by setting the print area might solve it?
>
> "Douglas J. Steele" wrote:
>
> > Are you trying to do this using Access, the database product that's part of
> > Office Professional (and the focus of this particular newsgroup)?
> >
> > If not, I'd recommend reposting your question to a newsgroup related to
> > Excel.
> >
> > If so, how are you reading the workbooks? If you're using VBA code, post it.
> >
> > --
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele
> > (no e-mails, please!)
> >
> >
> > "Arnie" <(E-Mail Removed)> wrote in message
> > news:2879FE28-9F83-4424-A1FC-(E-Mail Removed)...
> > > hi guys and gals,
> > > i am trying to read in several excel workbooks which seems to be working
> > > however,
> > > one file i have tried to read in i get this
> > >
> > > This table contains cells that are outside the range of cells defined in
> > > this spreadsheet. (Error 3673)
> > >
> > > i suspect i could define usable cells in the spreadsheet before it is read
> > > in just in case there is a problem. the cells in question are A1:P7 (they
> > > do
> > > not always have data in them but my coding alows for that) but i don't
> > > know
> > > how to do it.
> > >
> > > many thanks in advance
> > >
> > > Arnie
> >
> >
> >

thanks for that but i have already done that.
maybe i should explain a bit clearer
on most spreadsheets my code works fine i can read in all the data.
However these spreadsheets are produced by another program and the data is
not always the same (which i have accounted for).
the max cells i import are A1:P7 but not all of them i.e. i never import
A1,A2,A3,A4 but i do import A5 etc etc to P7.
Some of the spreadsheets only have data up to J7 and nothing in L,M,N,O,P
which seems to be fine my code just ignores it by importing Nulls (which i
can deal with)
My problem is when i get this message on some of the spreadsheets.

This table contains cells that are outside the range of cells defined in
this spreadsheet. (Error 3673)

i take it at the beginning of my code i somehow need to define whichever
spreadsheet i am importing cells A1:P7 will not cause this error.

"ryguy7272" wrote:

> This is how I do it:
> DoCmd.DeleteObject acTable, "tblYourTableName"
> DoCmd.TransferSpreadsheet acImport, 8, " tblYourTableName ",
> "C:\TempFolder\RestOfPathHere\ExcelFileName", True, "Sheet!A1:J100"
>
> Here is another example:
> http://www.xtremevbtalk.com/showthread.php?t=73618
>
> Finally, you could set up the process as a macro, get that working, then
> save the macro as a module to see the code. Great learning experience!!
>
> HTH,
> Ryan---
> --
> Ryan---
> If this information was helpful, please indicate this by clicking ''Yes''.
>
>
> "Arnie" wrote:
>
> > Douglas hi
> > yes i am using Access part of the code i use is
> > DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTemp_V1",
> > strFile, , "Calls!K2:K2"
> >
> > the spreadsheet (in this case only has data in it up to J) i am trying to
> > read is causing the below error i hoped there was a way of defining the cells
> > perhaps by setting the print area might solve it?
> >
> > "Douglas J. Steele" wrote:
> >
> > > Are you trying to do this using Access, the database product that's part of
> > > Office Professional (and the focus of this particular newsgroup)?
> > >
> > > If not, I'd recommend reposting your question to a newsgroup related to
> > > Excel.
> > >
> > > If so, how are you reading the workbooks? If you're using VBA code, post it.
> > >
> > > --
> > > Doug Steele, Microsoft Access MVP
> > > http://I.Am/DougSteele
> > > (no e-mails, please!)
> > >
> > >
> > > "Arnie" <(E-Mail Removed)> wrote in message
> > > news:2879FE28-9F83-4424-A1FC-(E-Mail Removed)...
> > > > hi guys and gals,
> > > > i am trying to read in several excel workbooks which seems to be working
> > > > however,
> > > > one file i have tried to read in i get this
> > > >
> > > > This table contains cells that are outside the range of cells defined in
> > > > this spreadsheet. (Error 3673)
> > > >
> > > > i suspect i could define usable cells in the spreadsheet before it is read
> > > > in just in case there is a problem. the cells in question are A1:P7 (they
> > > > do
> > > > not always have data in them but my coding alows for that) but i don't
> > > > know
> > > > how to do it.
> > > >
> > > > many thanks in advance
> > > >
> > > > Arnie
> > >
> > >
> > >

Error 3673 seems to be somewhat esoteric. I Googled it, and found next to
nothing on the topic. All I can say for sure is that I haven't dealt with
that error before. Maybe you can import a range beyond the range that you
are importing now, and just deal with the nulls in a Query. I do that all
the time, with great success. If there are some weird characters in your
spreadsheet, that may be causing the error. From experience, I know Excel
will handle pretty much everything you throw at it. Access, on the other
had, throws a fit if the data is not just right. This is for good reason.
Access data should be atomic (smallest unit possible) and of a specific type.
This will ensure data integrity. That is exactly what you want!


Good luck,
Ryan---



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Arnie" wrote:

> thanks for that but i have already done that.
> maybe i should explain a bit clearer
> on most spreadsheets my code works fine i can read in all the data.
> However these spreadsheets are produced by another program and the data is
> not always the same (which i have accounted for).
> the max cells i import are A1:P7 but not all of them i.e. i never import
> A1,A2,A3,A4 but i do import A5 etc etc to P7.
> Some of the spreadsheets only have data up to J7 and nothing in L,M,N,O,P
> which seems to be fine my code just ignores it by importing Nulls (which i
> can deal with)
> My problem is when i get this message on some of the spreadsheets.
>
> This table contains cells that are outside the range of cells defined in
> this spreadsheet. (Error 3673)
>
> i take it at the beginning of my code i somehow need to define whichever
> spreadsheet i am importing cells A1:P7 will not cause this error.
>
> "ryguy7272" wrote:
>
> > This is how I do it:
> > DoCmd.DeleteObject acTable, "tblYourTableName"
> > DoCmd.TransferSpreadsheet acImport, 8, " tblYourTableName ",
> > "C:\TempFolder\RestOfPathHere\ExcelFileName", True, "Sheet!A1:J100"
> >
> > Here is another example:
> > http://www.xtremevbtalk.com/showthread.php?t=73618
> >
> > Finally, you could set up the process as a macro, get that working, then
> > save the macro as a module to see the code. Great learning experience!!
> >
> > HTH,
> > Ryan---
> > --
> > Ryan---
> > If this information was helpful, please indicate this by clicking ''Yes''.
> >
> >
> > "Arnie" wrote:
> >
> > > Douglas hi
> > > yes i am using Access part of the code i use is
> > > DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTemp_V1",
> > > strFile, , "Calls!K2:K2"
> > >
> > > the spreadsheet (in this case only has data in it up to J) i am trying to
> > > read is causing the below error i hoped there was a way of defining the cells
> > > perhaps by setting the print area might solve it?
> > >
> > > "Douglas J. Steele" wrote:
> > >
> > > > Are you trying to do this using Access, the database product that's part of
> > > > Office Professional (and the focus of this particular newsgroup)?
> > > >
> > > > If not, I'd recommend reposting your question to a newsgroup related to
> > > > Excel.
> > > >
> > > > If so, how are you reading the workbooks? If you're using VBA code, post it.
> > > >
> > > > --
> > > > Doug Steele, Microsoft Access MVP
> > > > http://I.Am/DougSteele
> > > > (no e-mails, please!)
> > > >
> > > >
> > > > "Arnie" <(E-Mail Removed)> wrote in message
> > > > news:2879FE28-9F83-4424-A1FC-(E-Mail Removed)...
> > > > > hi guys and gals,
> > > > > i am trying to read in several excel workbooks which seems to be working
> > > > > however,
> > > > > one file i have tried to read in i get this
> > > > >
> > > > > This table contains cells that are outside the range of cells defined in
> > > > > this spreadsheet. (Error 3673)
> > > > >
> > > > > i suspect i could define usable cells in the spreadsheet before it is read
> > > > > in just in case there is a problem. the cells in question are A1:P7 (they
> > > > > do
> > > > > not always have data in them but my coding alows for that) but i don't
> > > > > know
> > > > > how to do it.
> > > > >
> > > > > many thanks in advance
> > > > >
> > > > > Arnie
> > > >
> > > >
> > > >