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]

I found this:

http://support.microsoft.com/kb/925893

and this:

http://support.microsoft.com/kb/327006

 

[DayDay]

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.

Oh man! This is crazy

Ok thanks for all your help Andrew.
If anyone else can help me I would really appreciate it.

 

[Andrew Poulsom]

Did you see the links I posted?

 

[DayDay]

I found this:

http://support.microsoft.com/kb/925893

and this:

http://support.microsoft.com/kb/327006

I think I know what's wrong Andrew!

The .xls files I am reading data from are being automatically generated from Avaya CMS Supervisor.

These files are basically csv files but are generated with a .xls extension!

This could be the reason, if so it puts a rather large spanner in my works!!!!!!!

 

[Andrew Poulsom]

That explains it. You can't link to a csv file without opening it in Excel. Only links to true Excel workbooks can be updated when the file is closed.

 

[DayDay]

That explains it. You can't link to a csv file without opening it in Excel. Only links to true Excel workbooks can be updated when the file is closed.

Mystery over, I am in the process of downloading a batch CSV to XLS convertor that can be run in the scheduler once the CSV is extracted

You have been most helpful and a credit to the community.

Thanks Andrew

 

[lee_alkureishi]

Sorry for dragging up an old thread, but this is exactly the same problem I have.

Can anyone recommend a program (ideally, a single-click solution) to convert a csv file (or group of files) to xls format? Is there a way to make a .bat file for this purpose?

I'm trying to link to an auto-generated file, and want the process to be as streamlined as possible for the end-user. Opening the csv, saving as xls, then opening the other spreadsheet, is too many steps!

Thanks in advance,

Lee

 

[Andrew Poulsom]

This may help you:

http://www.rondebruin.nl/csv.htm