DayDay
Board Regular
- Joined
- Jan 30, 2007
- Messages
- 64
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
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