Ignore Error '1004' help

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.......

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

"skito1" <skito1.2biv3i_1153866009.0297@excelforum-nospam.com> wrote in
message news:skito1.2biv3i_1153866009.0297@excelforum-nospam.com...
>
> 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.......
>
>
> --
> skito1
> ------------------------------------------------------------------------
> skito1's Profile:
http://www.excelforum.com/member.php...o&userid=36778
> View this thread: http://www.excelforum.com/showthread...hreadid=564936
>