Ignore Error '1004' help

[skito1]

The following code allows me to insert multiple .xls files into my MSSQL
database. The problem I have is that when a file is not found the macro
stops with runtime error '1004' file not found. How or where can I fix
this. I have tried *On Error Resume Next* but then run into more
strange errors (think I am not adding in the right place). I would like
to just go to next file if it is unable to find file specified. I use a
Excel Add-in "ExcelSQL" which works great. Currently we have to have
all 120 files in place before the program can run, this is not always
possible for us.

Workbooks.Open Filename:="C:\2701.xls"
Range("A1").AddComment
Range("A1").Comment.Visible = False
Range("A1").Comment.Text Text:= _
"!SQL!GLAN:10,10 !ACTION!" & Chr(10) & "DELETE FROM sys_check
WHERE site_id = ('{A1}')" & Chr(10) & "INSERT INTO sys_check (site_id,
bad_pgs, good_pgs, sql_used, d_space, bu_size, bu_date, c_space,
client_count, bu_status)" & Chr(10) & "VALUES ('{A1}', '{A5}', '{A4}',
'{A7}', (LEFT(RIGHT('{A3}', 26),15)), (LEFT(RIGHT('{A6}', 32),15)),
(RIGHT('{A10}', 11)), (LEFT(RIGHT('{A2}', 26),15)), '{A8}',
(RIGHT(LEFT('{A9}', 8),1)))" & Chr(10) & "" _
, Start:=200
Range("A1").Select
Application.Run "ExecuteSelectedSQLStatements"
ActiveWindow.Close SaveChanges:=False


Workbooks.Open Filename:="C:\2301.xls"
Range("A1").AddComment
Range("A1").Comment.Visible = False
Range("A1").Comment.Text Text:= _
"!SQL!GLAN:10,10 !ACTION!" & Chr(10) & "DELETE FROM sys_check
WHERE site_id = ('{A1}')" & Chr(10) & "INSERT INTO sys_check (site_id,
bad_pgs, good_pgs, sql_used, d_space, bu_size, bu_date, c_space,
client_count, bu_status)" & Chr(10) & "VALUES ('{A1}', '{A5}', '{A4}',
'{A7}', (LEFT(RIGHT('{A3}', 26),15)), (LEFT(RIGHT('{A6}', 32),15)),
(RIGHT('{A10}', 11)), (LEFT(RIGHT('{A2}', 26),15)), '{A8}',
(RIGHT(LEFT('{A9}', 8),1)))" & Chr(10) & "" _
, Start:=200
Range("A1").Select
Application.Run "ExecuteSelectedSQLStatements"
ActiveWindow.Close SaveChanges:=False


Workbooks.Open Filename:="C:\1050.xls"
Range("A1").AddComment
Range("A1").Comment.Visible = False
Range("A1").Comment.Text Text:= _
"!SQL!GLAN:10,10 !ACTION!" & Chr(10) & "DELETE FROM sys_check
WHERE site_id = ('{A1}')" & Chr(10) & "INSERT INTO sys_check (site_id,
bad_pgs, good_pgs, sql_used, d_space, bu_size, bu_date, c_space,
client_count, bu_status)" & Chr(10) & "VALUES ('{A1}', '{A5}', '{A4}',
'{A7}', (LEFT(RIGHT('{A3}', 26),15)), (LEFT(RIGHT('{A6}', 32),15)),
(RIGHT('{A10}', 11)), (LEFT(RIGHT('{A2}', 26),15)), '{A8}',
(RIGHT(LEFT('{A9}', 8),1)))" & Chr(10) & "" _
, Start:=200
Range("A1").Select
Application.Run "ExecuteSelectedSQLStatements"
ActiveWindow.Close SaveChanges:=False

-Any help or suggestions will be appreciated and keep me sane.......

 

[NickHK]

How about something like:

Const FileList as string="C:\2701.xls,C:\2301.xls,C:\1050.xls"
Dim FileNames() As Variant
Dim i as long
Dim WB as workbook

Filename=split(filelist,",") 'Or fill from a range
For i=0 to ubound(filenames)
On error resume next
set wb=workbooks.open(filename(i))
on error goto 0
if not wb is nothing then
with wb.worksheets(1).range("A1").AddComment("!SQL!GLAN:10,10
!ACTI....etc")
.visible=false
Application.Run "ExecuteSelectedSQLStatements"
end with
wb.close SaveChanges:=False
end if
next

NickHK