Excel/VBA question.

[mace¬]

Can someone explain this simple bit of VBA to me please. I'm not the best at it and I'm trying to get to the bottom of why a piece of data isn't always being imported into Access from a spreadsheet.

Call Functions.TransferSpreadsheet("TEMPCRDATA", ReviewPath, True, "rngWPRData")

If I am right rngWPRData is the range of cells to import so......

Where will I find what cells have been defined to be imported in the range "rngWPRData"??

I can't find anything in Access the VBA or in the spreadsheet itself.

cheers
Pete

 

[biojo]

in the spreadsheet click the arrow

that should show any cell references thats named

has the code ever worked and are you sure it needs a "call functions"

 

[mace¬]

Top man found it.

The code works intermittenly. The scenario is.....
We have a worksheet where you input a closed date. We then have a metrics sheet in the same workbook that compiles all the data that we import into Access.

Problem is occasionally a closed date is input but when we do the import into Access the closed date isn't imported. The cell it is importing from has the following formula and is in the rngWPRDData.

=IF(OR(Review!$D$99 <= 0),"",Review!$D$101)

Funny thing is, if you then open the spreadsheet and close it again the close data imports fine.

It is also only happening for one person who puts the closed date in and doesn't happen on every spreadsheet he puts a closed date on. The same template spreadsheet is used each time.

The onyl thing I can think is that when he inputs the closed date he is somehow closing the speadsheet down and excel isn't doing the update on the metrics spreadsheet. When we open that spreadsheet back up it does all the required updates and therefore works when we run the import in access again. Is that possible?

 

[biojo]

ha, owts possible with some users, you would need to watch his every step and compare it to a user that doesn't have the issue.

Then use to code, to stop him from doing it. so one thas says if excel is closed before xxx then do this etc...

does that date field import fine for others, if so are you sure the fields are set up same in access and excel, as access sometimes stores them in long values date & time, but when viewed in a table only shows a short version

 

[mace¬]

He is the only person who uses the access database but multiple people a closed date into Excel spreadsheets.

Things have moved on now.... I think it is something to do with the VBA in the spreadsheet. I am going to get him to put an end date in as he would normally and see if we can recreate it. I think the VBA when closing the Excel spreadsheet isn't be called. This is where it does the setup of the data from the review spreadsheet into the Metrics one.

(If that makes any sense).

thanks for your help so far.