Pulling data only if file exists.

[DayDay]

I am using the following VB code to check if a file exists and if so add a formula in my workbook providing data from that file.

I have pre-pulled raw data in the directory "H:\BusinessRpt\Test for scripts\" which is named firstly by the day of the month and then what data it is.
- i.e/ 13_CAI_AgentStats.xls

The idea of my code is to check firstly whether the file exists (i.e has the data been pulled yet) and then if so to extract the data I need from the correct sheet and if not to add a zero instead.

I thought all was ok, but if the raw workbook is not open I get a #REF error.

I have looked at some posts for getting info from a closed workbook on this site but I can't get my head around incorporating them into my code!!

Please help!!

Code listed below

------

Sub Check_File_Exists()

For date_test = 1 To 31

If Dir("H:\BusinessRpt\Test for scripts\" & date_test & "_CAI_AgentStats.xls") <> "" Then

Range("AE" & date_test + 11).Formula = "='H:\BusinessRpt\Test for scripts\[" & date_test & "_CAI_AgentStats.xls]" & date_test & "_CAI_AgentStats'!$D$4"

Else

Range("AE" & date_test + 11) = 0

End If

Next date_test

End Sub

 

[Andrew Poulsom]

Can you post a sample formula as it appears when the source workbook is open and as it appears when the source workbook is closed?

 

[DayDay]

Sure,

when closed:

='H:\BusinessRpt\Test for scripts\[13_CAI_AgentStats.xls]13_CAI_AgentStats'!$D$4

(also in the links section it says "Warning. Open source to update links")

when open:

='13_CAI_AgentStats.xls'!$D$4

Which to me seems to be correct??

 

[Andrew Poulsom]

Does that formula work when the workbook is closed? I would expect it to, and I have never seen that warning before.

 

[DayDay]

No! This is why I am having trouble, I would expect this to work.

What happens is I run the code, it replaces the formulas correctly but the end values appear as #REF errors.

I then go into Edit Menu and Links and the links show as Status: Unknown.
If I click update values it gives me the following:

Excel cannot update one or more values in this workbook. To update the links, open all the link source files (Edit menu, Links command). To be sure all calculations are updated, press F9.

Then I click ok and the status of the files changes to Warning: Open source to update values. So I click the open source button and all the source files open which is a nightmare if you have 31 days data.

Surely there must be a workaround!

David

 

[DayDay]

AHA!!!

I change the line in the code:

Range("AE" & date_test + 11).Formula = "='H:\BusinessRpt\Test for scripts\[" & date_test & "_CAI_AgentStats.xls]" & date_test & "_CAI_AgentStats'!$D$4"

with:

Range("AE" & date_test + 11).Value = "='H:\BusinessRpt\Test for scripts\[" & date_test & "_CAI_AgentStats.xls]" & date_test & "_CAI_AgentStats'!$D$4"

Oh well, strange!!!

Thanks for all your help Andrew

 

[Andrew Poulsom]

Are the source workbooks password protected?

 

[Andrew Poulsom]

AHA!!!

I change the line in the code:

Range("AE" & date_test + 11).Formula = "='H:\BusinessRpt\Test for scripts\[" & date_test & "_CAI_AgentStats.xls]" & date_test & "_CAI_AgentStats'!$D$4"

with:

Range("AE" & date_test + 11).Value = "='H:\BusinessRpt\Test for scripts\[" & date_test & "_CAI_AgentStats.xls]" & date_test & "_CAI_AgentStats'!$D$4"

Oh well, strange!!!

Thanks for all your help Andrew

Bizarre!

 

[DayDay]

Bizarre!

Ok I was ahead of myself. Its not that the change worked. It's if they have been opened once already then they update automatically until you close the main workbook and re-open it and run the code!!!!!!

Although, I am at a loss to understand why the .Formula change to .Value makes no difference!

And no the workbooks are not protected.

ANDREW, HELP!!!

 

[Andrew Poulsom]

I can't reproduce your problem in Excel 2000. In VBA I can add a formula like yours without getting a #REF! error and the Link Status is A.

So I'm not sure I can help any more.