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