Embedded Excel sheet viewer

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to use an Access Form to display specific ranges from Excel
sheets in read-only or print preview format?

My goal is to use a tab control in an Access form to select from an
assortment of excel sheets, and I want the user to be able to scroll through
and view the linked sheets. I am hoping to do this within the Access
application, not to open an external instance of Excel to do this, although
Excel is installed on the user's pc.

I have no skills with OLE, but have tried tinkering with settings like the
Size Mode, verb index and action property, but I have not been able to get a
specific range of linked sheets to display in the control: (the sheet is
often cropped off at an arbitrary row/column).

Does anyone have a suggestion for how to view Excel sheets from within
Access?
Can someone point me to a good link for learning the basics of coding for
OLE spreadsheets? Or is there a better way of accomplishing this?

TIA

T Kirtley
 
Hi

The following code will create a linked table named "ExcelView" which is
linked to the range A7:F22 in the given Excel file:

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel8, _
"ExcelView", "C:\Data\MySpreadSheet.xls", , "A7:F22"

You can then create an unbound form with a subform control, and set the
subform's SourceObject to:
Table.ExcelView

Of course, the filename and range arguments in the TransferSpreadsheet
command can be variables, so you could put two textboxes on your form, one
for the filename and one for the range, and a command button named cmdView.
The View button needs to perform the following steps:

1) Set the SourceObject of the subform control to "". This will close the
linked table if it is open.

2) Delete the table ExcelView (if it already exists)

3) Execute the DoCmd.TransferSpreadsheet command to link the specified range
in the given Excel file.

4) Set the SourceObject of the subform back to "Table.ExcelView"
 
Hi Graham,

Thanks for the response, but I am not trying to import the data from the
spreadsheets, I want to display the spreadsheet contents as formatted in
Excel. Ideally the form would display a print preview of the excel sheet that
was chosen.

The path I am currently pursuing is to parse through the excel sheets and
create pdf files from the print output, and then to open the pdf file in the
Access form. This requires an extra step to create the equivalent of a 'print
preview' output, but does get the desired output.

However, I have not yet figured out how to open either an Excel sheet or an
Adobe pdf that is embedded in the form; (not in a separate instance of the
native application). Can an Access form support an embedded instance of an
Excel sheet or a pdf file?

Thanks again for your help.

T Kirtley

Graham Mandeno said:
Hi

The following code will create a linked table named "ExcelView" which is
linked to the range A7:F22 in the given Excel file:

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel8, _
"ExcelView", "C:\Data\MySpreadSheet.xls", , "A7:F22"

You can then create an unbound form with a subform control, and set the
subform's SourceObject to:
Table.ExcelView

Of course, the filename and range arguments in the TransferSpreadsheet
command can be variables, so you could put two textboxes on your form, one
for the filename and one for the range, and a command button named cmdView.
The View button needs to perform the following steps:

1) Set the SourceObject of the subform control to "". This will close the
linked table if it is open.

2) Delete the table ExcelView (if it already exists)

3) Execute the DoCmd.TransferSpreadsheet command to link the specified range
in the given Excel file.

4) Set the SourceObject of the subform back to "Table.ExcelView"
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


Kirtley said:
Is it possible to use an Access Form to display specific ranges from Excel
sheets in read-only or print preview format?

My goal is to use a tab control in an Access form to select from an
assortment of excel sheets, and I want the user to be able to scroll
through
and view the linked sheets. I am hoping to do this within the Access
application, not to open an external instance of Excel to do this,
although
Excel is installed on the user's pc.

I have no skills with OLE, but have tried tinkering with settings like the
Size Mode, verb index and action property, but I have not been able to get
a
specific range of linked sheets to display in the control: (the sheet is
often cropped off at an arbitrary row/column).

Does anyone have a suggestion for how to view Excel sheets from within
Access?
Can someone point me to a good link for learning the basics of coding for
OLE spreadsheets? Or is there a better way of accomplishing this?

TIA

T Kirtley
Click to expand...
Click to expand...
 
I wasn't suggesting that you should import the data, but that you should
create a temporary link to it, so that your linked table can be displayed in
a subform.

You coud use an unbound object frame to display a linked spreadsheet range,
like this:

Private Sub cmdLink_Click()
OLE1.Class = "Excel.Sheet" ' Set class name.
OLE1.OLETypeAllowed = acOLELinked ' Specify type of object.
OLE1.SourceDoc = txtSourceDoc ' Specify source file.
OLE1.SourceItem = Nz(txtSourceItem, "") ' Specify data to create link to.
OLE1.Action = acOLECreateLink ' Create linked object.
OLE1.SizeMode = acOLESizeZoom ' Adjust control size.
End Sub

Your filename in in a textbox named txtSourceDoc, and your range in in
txtSourceItem. I think the range must be in RC format - for example:
R2C3:R10C6

I don't know how you will go with scrolling though.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

T Kirtley said:
Hi Graham,

Thanks for the response, but I am not trying to import the data from the
spreadsheets, I want to display the spreadsheet contents as formatted in
Excel. Ideally the form would display a print preview of the excel sheet
that
was chosen.

The path I am currently pursuing is to parse through the excel sheets and
create pdf files from the print output, and then to open the pdf file in
the
Access form. This requires an extra step to create the equivalent of a
'print
preview' output, but does get the desired output.

However, I have not yet figured out how to open either an Excel sheet or
an
Adobe pdf that is embedded in the form; (not in a separate instance of the
native application). Can an Access form support an embedded instance of an
Excel sheet or a pdf file?

Thanks again for your help.

T Kirtley

Graham Mandeno said:
Hi

The following code will create a linked table named "ExcelView" which is
linked to the range A7:F22 in the given Excel file:

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel8, _
"ExcelView", "C:\Data\MySpreadSheet.xls", , "A7:F22"

You can then create an unbound form with a subform control, and set the
subform's SourceObject to:
Table.ExcelView

Of course, the filename and range arguments in the TransferSpreadsheet
command can be variables, so you could put two textboxes on your form,
one
for the filename and one for the range, and a command button named
cmdView.
The View button needs to perform the following steps:

1) Set the SourceObject of the subform control to "". This will close
the
linked table if it is open.

2) Delete the table ExcelView (if it already exists)

3) Execute the DoCmd.TransferSpreadsheet command to link the specified
range
in the given Excel file.

4) Set the SourceObject of the subform back to "Table.ExcelView"
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


Kirtley said:
Is it possible to use an Access Form to display specific ranges from
Excel
sheets in read-only or print preview format?

My goal is to use a tab control in an Access form to select from an
assortment of excel sheets, and I want the user to be able to scroll
through
and view the linked sheets. I am hoping to do this within the Access
application, not to open an external instance of Excel to do this,
although
Excel is installed on the user's pc.

I have no skills with OLE, but have tried tinkering with settings like
the
Size Mode, verb index and action property, but I have not been able to
get
a
specific range of linked sheets to display in the control: (the sheet
is
often cropped off at an arbitrary row/column).

Does anyone have a suggestion for how to view Excel sheets from within
Access?
Can someone point me to a good link for learning the basics of coding
for
OLE spreadsheets? Or is there a better way of accomplishing this?

TIA

T Kirtley
Click to expand...
Click to expand...
Click to expand...
 
Hi Graham,

Thanks for the follow-up. I do appreciate it.

I tried again with your code but I run into problems when the the Action is
set to "acOLECreateLink" and I get "run-time error 2793: Microsoft Access
can't perform the operation specified in the Action property of the Visual
Basic procedure you're trying to run."

I have also tried using actions of " acOLECreateEmbed" and acOLECreateNew"
and get the same error. Are there any other settings that need to be
specified before creating a link to an Excel sheet ? I'm totally new with OLE
so I could be missing something very basic.

However, if it won't be possible to scroll with a linked Excel sheet, I'm
thinking that a linked pdf is the more promissing route since this needs to
function as a report viewer. I can create pdf's from the Excel sheets and can
get the Adobe Reader to display the pdf's that have been individually
embedded in a form, but I cannot figure out how to dynamically re-use a
single OLE link on a form to display a series of selections.

Anyway, I am getting pretty far off the subject of the original post, and it
would be great if I could just get a link to an Excel sheet to work, and I
can take on the rest after I bone up a little on OLE.

Thanks again,

T Kirtley


Graham Mandeno said:
I wasn't suggesting that you should import the data, but that you should
create a temporary link to it, so that your linked table can be displayed in
a subform.

You coud use an unbound object frame to display a linked spreadsheet range,
like this:

Private Sub cmdLink_Click()
OLE1.Class = "Excel.Sheet" ' Set class name.
OLE1.OLETypeAllowed = acOLELinked ' Specify type of object.
OLE1.SourceDoc = txtSourceDoc ' Specify source file.
OLE1.SourceItem = Nz(txtSourceItem, "") ' Specify data to create link to.
OLE1.Action = acOLECreateLink ' Create linked object.
OLE1.SizeMode = acOLESizeZoom ' Adjust control size.
End Sub

Your filename in in a textbox named txtSourceDoc, and your range in in
txtSourceItem. I think the range must be in RC format - for example:
R2C3:R10C6

I don't know how you will go with scrolling though.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

T Kirtley said:
Hi Graham,

Thanks for the response, but I am not trying to import the data from the
spreadsheets, I want to display the spreadsheet contents as formatted in
Excel. Ideally the form would display a print preview of the excel sheet
that
was chosen.

The path I am currently pursuing is to parse through the excel sheets and
create pdf files from the print output, and then to open the pdf file in
the
Access form. This requires an extra step to create the equivalent of a
'print
preview' output, but does get the desired output.

However, I have not yet figured out how to open either an Excel sheet or
an
Adobe pdf that is embedded in the form; (not in a separate instance of the
native application). Can an Access form support an embedded instance of an
Excel sheet or a pdf file?

Thanks again for your help.

T Kirtley

Graham Mandeno said:
Hi

The following code will create a linked table named "ExcelView" which is
linked to the range A7:F22 in the given Excel file:

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel8, _
"ExcelView", "C:\Data\MySpreadSheet.xls", , "A7:F22"

You can then create an unbound form with a subform control, and set the
subform's SourceObject to:
Table.ExcelView

Of course, the filename and range arguments in the TransferSpreadsheet
command can be variables, so you could put two textboxes on your form,
one
for the filename and one for the range, and a command button named
cmdView.
The View button needs to perform the following steps:

1) Set the SourceObject of the subform control to "". This will close
the
linked table if it is open.

2) Delete the table ExcelView (if it already exists)

3) Execute the DoCmd.TransferSpreadsheet command to link the specified
range
in the given Excel file.

4) Set the SourceObject of the subform back to "Table.ExcelView"
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


Is it possible to use an Access Form to display specific ranges from
Excel
sheets in read-only or print preview format?

My goal is to use a tab control in an Access form to select from an
assortment of excel sheets, and I want the user to be able to scroll
through
and view the linked sheets. I am hoping to do this within the Access
application, not to open an external instance of Excel to do this,
although
Excel is installed on the user's pc.

I have no skills with OLE, but have tried tinkering with settings like
the
Size Mode, verb index and action property, but I have not been able to
get
a
specific range of linked sheets to display in the control: (the sheet
is
often cropped off at an arbitrary row/column).

Does anyone have a suggestion for how to view Excel sheets from within
Access?
Can someone point me to a good link for learning the basics of coding
for
OLE spreadsheets? Or is there a better way of accomplishing this?

TIA

T Kirtley
Click to expand...
Click to expand...
Click to expand...
 
You could try creating the unbound object frame control again, and be sure
to specify "Create from file" and "Link". Choose any Excel document as the
linked file. This should set up the control to work using the code I gave
you.

I'm sure there is a way to effect in-place scrolling for OLE controls, but
they're not something I use much, so I'm not an expert either. Sorry!
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

T Kirtley said:
Hi Graham,

Thanks for the follow-up. I do appreciate it.

I tried again with your code but I run into problems when the the Action
is
set to "acOLECreateLink" and I get "run-time error 2793: Microsoft Access
can't perform the operation specified in the Action property of the Visual
Basic procedure you're trying to run."

I have also tried using actions of " acOLECreateEmbed" and acOLECreateNew"
and get the same error. Are there any other settings that need to be
specified before creating a link to an Excel sheet ? I'm totally new with
OLE
so I could be missing something very basic.

However, if it won't be possible to scroll with a linked Excel sheet, I'm
thinking that a linked pdf is the more promissing route since this needs
to
function as a report viewer. I can create pdf's from the Excel sheets and
can
get the Adobe Reader to display the pdf's that have been individually
embedded in a form, but I cannot figure out how to dynamically re-use a
single OLE link on a form to display a series of selections.

Anyway, I am getting pretty far off the subject of the original post, and
it
would be great if I could just get a link to an Excel sheet to work, and
I
can take on the rest after I bone up a little on OLE.

Thanks again,

T Kirtley


Graham Mandeno said:
I wasn't suggesting that you should import the data, but that you should
create a temporary link to it, so that your linked table can be displayed
in
a subform.

You coud use an unbound object frame to display a linked spreadsheet
range,
like this:

Private Sub cmdLink_Click()
OLE1.Class = "Excel.Sheet" ' Set class name.
OLE1.OLETypeAllowed = acOLELinked ' Specify type of object.
OLE1.SourceDoc = txtSourceDoc ' Specify source file.
OLE1.SourceItem = Nz(txtSourceItem, "") ' Specify data to create link
to.
OLE1.Action = acOLECreateLink ' Create linked object.
OLE1.SizeMode = acOLESizeZoom ' Adjust control size.
End Sub

Your filename in in a textbox named txtSourceDoc, and your range in in
txtSourceItem. I think the range must be in RC format - for example:
R2C3:R10C6

I don't know how you will go with scrolling though.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

T Kirtley said:
Hi Graham,

Thanks for the response, but I am not trying to import the data from
the
spreadsheets, I want to display the spreadsheet contents as formatted
in
Excel. Ideally the form would display a print preview of the excel
sheet
that
was chosen.

The path I am currently pursuing is to parse through the excel sheets
and
create pdf files from the print output, and then to open the pdf file
in
the
Access form. This requires an extra step to create the equivalent of a
'print
preview' output, but does get the desired output.

However, I have not yet figured out how to open either an Excel sheet
or
an
Adobe pdf that is embedded in the form; (not in a separate instance of
the
native application). Can an Access form support an embedded instance of
an
Excel sheet or a pdf file?

Thanks again for your help.

T Kirtley

:

Hi

The following code will create a linked table named "ExcelView" which
is
linked to the range A7:F22 in the given Excel file:

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel8, _
"ExcelView", "C:\Data\MySpreadSheet.xls", , "A7:F22"

You can then create an unbound form with a subform control, and set
the
subform's SourceObject to:
Table.ExcelView

Of course, the filename and range arguments in the TransferSpreadsheet
command can be variables, so you could put two textboxes on your form,
one
for the filename and one for the range, and a command button named
cmdView.
The View button needs to perform the following steps:

1) Set the SourceObject of the subform control to "". This will close
the
linked table if it is open.

2) Delete the table ExcelView (if it already exists)

3) Execute the DoCmd.TransferSpreadsheet command to link the specified
range
in the given Excel file.

4) Set the SourceObject of the subform back to "Table.ExcelView"
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


Is it possible to use an Access Form to display specific ranges from
Excel
sheets in read-only or print preview format?

My goal is to use a tab control in an Access form to select from an
assortment of excel sheets, and I want the user to be able to
scroll
through
and view the linked sheets. I am hoping to do this within the Access
application, not to open an external instance of Excel to do this,
although
Excel is installed on the user's pc.

I have no skills with OLE, but have tried tinkering with settings
like
the
Size Mode, verb index and action property, but I have not been able
to
get
a
specific range of linked sheets to display in the control: (the
sheet
is
often cropped off at an arbitrary row/column).

Does anyone have a suggestion for how to view Excel sheets from
within
Access?
Can someone point me to a good link for learning the basics of
coding
for
OLE spreadsheets? Or is there a better way of accomplishing this?

TIA

T Kirtley
Click to expand...
Click to expand...
Click to expand...
 
Thanks Graham,

I found the problem: it turns out that OLE objects have to be Unlocked and
Enabled in order to use most OLE actions. The default properties for an
unbound object frame are Locked=True and Enabled=False. Once I got these set
right your code works fine.

Thanks again.

T Kirtley

Graham Mandeno said:
You could try creating the unbound object frame control again, and be sure
to specify "Create from file" and "Link". Choose any Excel document as the
linked file. This should set up the control to work using the code I gave
you.

I'm sure there is a way to effect in-place scrolling for OLE controls, but
they're not something I use much, so I'm not an expert either. Sorry!
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

T Kirtley said:
Hi Graham,

Thanks for the follow-up. I do appreciate it.

I tried again with your code but I run into problems when the the Action
is
set to "acOLECreateLink" and I get "run-time error 2793: Microsoft Access
can't perform the operation specified in the Action property of the Visual
Basic procedure you're trying to run."

I have also tried using actions of " acOLECreateEmbed" and acOLECreateNew"
and get the same error. Are there any other settings that need to be
specified before creating a link to an Excel sheet ? I'm totally new with
OLE
so I could be missing something very basic.

However, if it won't be possible to scroll with a linked Excel sheet, I'm
thinking that a linked pdf is the more promissing route since this needs
to
function as a report viewer. I can create pdf's from the Excel sheets and
can
get the Adobe Reader to display the pdf's that have been individually
embedded in a form, but I cannot figure out how to dynamically re-use a
single OLE link on a form to display a series of selections.

Anyway, I am getting pretty far off the subject of the original post, and
it
would be great if I could just get a link to an Excel sheet to work, and
I
can take on the rest after I bone up a little on OLE.

Thanks again,

T Kirtley


Graham Mandeno said:
I wasn't suggesting that you should import the data, but that you should
create a temporary link to it, so that your linked table can be displayed
in
a subform.

You coud use an unbound object frame to display a linked spreadsheet
range,
like this:

Private Sub cmdLink_Click()
OLE1.Class = "Excel.Sheet" ' Set class name.
OLE1.OLETypeAllowed = acOLELinked ' Specify type of object.
OLE1.SourceDoc = txtSourceDoc ' Specify source file.
OLE1.SourceItem = Nz(txtSourceItem, "") ' Specify data to create link
to.
OLE1.Action = acOLECreateLink ' Create linked object.
OLE1.SizeMode = acOLESizeZoom ' Adjust control size.
End Sub

Your filename in in a textbox named txtSourceDoc, and your range in in
txtSourceItem. I think the range must be in RC format - for example:
R2C3:R10C6

I don't know how you will go with scrolling though.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Thanks for the response, but I am not trying to import the data from
the
spreadsheets, I want to display the spreadsheet contents as formatted
in
Excel. Ideally the form would display a print preview of the excel
sheet
that
was chosen.

The path I am currently pursuing is to parse through the excel sheets
and
create pdf files from the print output, and then to open the pdf file
in
the
Access form. This requires an extra step to create the equivalent of a
'print
preview' output, but does get the desired output.

However, I have not yet figured out how to open either an Excel sheet
or
an
Adobe pdf that is embedded in the form; (not in a separate instance of
the
native application). Can an Access form support an embedded instance of
an
Excel sheet or a pdf file?

Thanks again for your help.

T Kirtley

:

Hi

The following code will create a linked table named "ExcelView" which
is
linked to the range A7:F22 in the given Excel file:

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel8, _
"ExcelView", "C:\Data\MySpreadSheet.xls", , "A7:F22"

You can then create an unbound form with a subform control, and set
the
subform's SourceObject to:
Table.ExcelView

Of course, the filename and range arguments in the TransferSpreadsheet
command can be variables, so you could put two textboxes on your form,
one
for the filename and one for the range, and a command button named
cmdView.
The View button needs to perform the following steps:

1) Set the SourceObject of the subform control to "". This will close
the
linked table if it is open.

2) Delete the table ExcelView (if it already exists)

3) Execute the DoCmd.TransferSpreadsheet command to link the specified
range
in the given Excel file.

4) Set the SourceObject of the subform back to "Table.ExcelView"
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


Is it possible to use an Access Form to display specific ranges from
Excel
sheets in read-only or print preview format?

My goal is to use a tab control in an Access form to select from an
assortment of excel sheets, and I want the user to be able to
scroll
through
and view the linked sheets. I am hoping to do this within the Access
application, not to open an external instance of Excel to do this,
although
Excel is installed on the user's pc.

I have no skills with OLE, but have tried tinkering with settings
like
the
Size Mode, verb index and action property, but I have not been able
to
get
a
specific range of linked sheets to display in the control: (the
sheet
is
often cropped off at an arbitrary row/column).

Does anyone have a suggestion for how to view Excel sheets from
within
Access?
Can someone point me to a good link for learning the basics of
coding
for
OLE spreadsheets? Or is there a better way of accomplishing this?

TIA

T Kirtley
Click to expand...
Click to expand...
Click to expand...
 
From the forms design view click on Bound Object Frame which is found on the
tool bar drag the bound object frame to your forms design. Then open Excel
and highlight and copy the range. Keeping Excel open return to Access click
in the Bound Object Frame and Paste Special, following the wizard, to link or
embed the spreadsheet range on your form. You can repeat this for each of
your spreadsheet tabs.

You can get these instructions from Access help screen. Ask for "Bound
Object Frames" then Show All and print the instructions. I think around page
8 or 9 it explains in more detail what I have tried to explain above.

As for print/preview I haven't figured that one out yet. It has something
to do with SourceDoc and SourceItem property expressions.
 
Back
Top