Access 2000 with linked Excel spreadsheets underneath and an Excel PivotTable on top

[Paul]

Hello

About me: I have followed an "introduction to Access" training and
have no knowledge of VB so
apologies for the long mail and... please be gentle ;-)

Here's the situation:
1. From Access 2000 I link a couple of dozen Excel worksheets (the
sheets are linked, not imported)


2. Inside Access a few queries do their stuff (no manual intervention
at all)
3. An Excel PivotTable points at the final query in Access
4. The users need to be able to change data in an underlying Excel
worksheet, save it and then immediately refresh the view in the Excel
PivotTable

Info: some of the spreadsheets contain information exported from
legacy systems,
so some numbers are text.

Issue:
the users' point of view is that if a user has made changes to
numbers in the original Excel worksheets, why is it not showing up in
the
consolidated report?

Tried and failed:
5. I have tried to force certain fields to be numbers rather than
text
within the queries, but this does not work when the link is to an
Excel worksheet rather than
to an Access table.
6. I could not propose a solution where the Excel worksheets are
imported
and corrected within Access because my users want to make the changes
in their own Excel files
and see the effects of these changes in the PivotTable with "just two
clicks of a button"


So, finally ... my questions
A. I know that if the users use the " ' " ('label') prefix when
changing
numbers in Excel (at least for those data imported from legacy
systems)
that changes will be OK, but I cannot guarantee that my users will do
this
consistently. Is there a way to force my queries to include both the
text
and the (manually entered) numbers please without using VB?
B.
Using "Design View" in Access to look at the linked worksheets, I see
that
sometimes many extra fields are reported by Access. This causes
problems with my union
queries.
It could be that the person preparing their original Excel file used
a few
extra columns for their own use and then deleted the content of these
extra
columns before forwarding the Excel file to me.So at the moment we
use manual validation
for all of the worksheets .
Can I solve this issue with a "way of working" in Excel or with a
query in
Access 2000 (again without VB)?

Thank you very much.

Best regards
Paul

 

[John Nurick]

Hi Paul,

A robust solution to this is going to need a lot of VBA to inspect and
validate the data in the Excel sheets. A good half measure might be to
work up an Excel macro that you could either issue to your users or run
yourself on each sheet before linking it. The macro would need to

1) Prefix numbers with an apostrophe
2) Prevent additional columns being imported.

The problem when users add or delete columns happens because when you
tell Access to link a worksheet, it relies on the worksheet's UsedRange
property to decide which columns and rows constitute the table you want
it to import. In some circumstances, particularly if users have been
moving the data round, the UsedRange can end up bigger than the actual
table. One way to avoid this problem is to create a named range in the
workbook that refers to the cells making up the table, and then link to
this range rather than to the worksheet. Something like this should take
care of both:

Dim raC As Excel.Range
Dim raT As Excel.Range

'Start at A1 and expand to the whole table
Set raT = Activesheet.Cells(1,1).CurrentRegion

'Name it
ActiveWorkbook.Names.Add Name:="TheTable", _
RefersTo:="=" & ActiveSheet.Name & "!" _
& raT.Address

'Add apostrophes
For Each raC In raT.Cells
If IsNumeric(raC.Formula) Then
raC.Formula = "'" & raC.Formula
End If
Next

If you experiment with the Excel macro recorder and viewing the VBA code
it produces, you'll soon discover how to put this code into an Excel
module and make it work.

 

[Paul]

Many thanks, I will pursue the macro suggestion first
Best regards
Paul