Excel FileExists macro problem....

I

Inabus

Guys/Gals,
Bit of backgroup to help out:
I am a ZENworks administrator for my company and we have 9 differen
sites. On each site I ran a ZEN application that reported whether o
not it ran on user's machines. If the application ran then the use
doesnt have Windows XP SP1 installed.

Problem:
I have created a single workbook that will import all the log file
from each site and compile them into 9 different tabs in an exce
workbook. The macro will successfully run however I am trying to expan
the macro to include error checking and more touchy fealy funtions.

When I run the macro it asks for the file name which I place into
variable called "Report". Currently the macro will then run the belo
command to connect to the log file on "sitea" and import this file.

With ActiveSheet.QueryTables.Add(Connection:="TEXT;\\Server UNC path\
& Report, Destination:=Sheets("sitea")

As I currently stand what I wanted to do was something like th
following:

If fs.FileExists("\\Server UNC path" & Report) = True Then

After the then I want to run the above Activesheet.Query macro, if th
if statement is false then I want it to write "File Does Not Exist
into Cell A1 on, in this case the Site1 worksheet

I am currently a bit stumped with this as I cant get the fileexis
funtion to work.

Is anyone able to help me with this or tell me where I am going wrong?

Regards,
Pau
 
I

Inabus

Thanks for that, however I did find it before and being a n00
programmer, as in this is my 1st attempt doing macro's I wasnt sure ho
to integrate that function into my macro and where / how to put in al
the information in my macro to call the function as well as how t
configure the function to search for the file I want.

Pau
 
I

Inabus

OK got it all working now thanks for all your help, calling a functio
for formatting the 9 sheets and the function for fileexists so agai
thanks!

One other thing I would like to know, how can I now attach my complete
macro to a command button on my intro sheet?

Regards,
Pau
 
I

Inabus

Er again, dont worry figured it out myself :)

All sorted now, macro runs from a button on the excel sheet.

Job done :)

Thanks guys!

Pau
 
Top