Refresh BackgroundQuery Excel Error

[PugnaxX]

I have hundreds of Excel Files of the same format, in the same folder that I need to extract data from. There are 5 columns 16 rows in length that I need to pull from each file into the one consolidated file. The way I am trying to format it is:

S/N CH LP TJ RJ DJ
1 1 # # # #
1 2 # # # #
1 3 # # # #
.... down to 16
1 16 # # # #
2 1 # # # #
2 2 # # # #
and so on...
So basically I have a certain number of rows for one file and then I will need Excel to drop down and insert the new file data immediately following the previous.

The have coded enough so that I can query each file in the directory, grab a column of data and put it into one column It works for a while and then crashes on ".Refresh BackgroundQuery:=False" If I set .Refresh...Query:=True, then it crashes after 65 files. I have checked the files around that area in the directory (assumming I know how Excel is going through the folder) and the files don't appear to be different. If I comment out the .Refresh... after I get an error, the program continues.


The code that I have thus far is as follows:
Sub DemoDIR()
Dim mFile
n = 0
'The Following steps through each .xls file in the dir
mFile = Dir("G:\Public\CT SCAN\MOOG TECH TOOLS ARCHIVE\FO6510 SN XYZ\*.xls")
Do While mFile <> ""
Selection.Activate
Range("A1").Select
'Query Code to pull one column from each file and put into single worksheet of the summary workbook

ActiveCell.SpecialCells(xlCellTypeLastCell).Offset(1, 0).Select
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=G:\Public\CT SCAN\MOOG TECH TOOLS ARCHIVE\FO6510 SN XYZ\" & mFile & "" _
, _
";Mode=Share Deny Write;Extended Properties=""HDR=NO;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OL" _
, _
"EDBatabase Password="""";Jet OLEDB:Engine Type=35;Jet OLEDBatabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLED" _
, _
"B:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Databas" _
, _
"e=False;Jet OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=ActiveCell)
.CommandType = xlCmdTable
.CommandText = Array("lCell")
.Name = "FO6510"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = _
"G:\Public\CT SCAN\MOOG TECH TOOLS ARCHIVE\FO6510 SN XYZ\" & mFile & ""
.Refresh BackgroundQuery:=False
End With
n = n + 1

mFile = Dir
Loop
MsgBox n & ("Excel Files are Present in the Directory!!")

End Sub

I'm wondering if I have a simple mistake in the code? If I don't and it may be a file issue, is there any way to get Excel to skip a file if it has trouble with it as I would like to incorporate this anyway. Any input is greatly appreciated!! Thanks so much!

PugnaxX

 

[combo]

Each querytable in your code is shifted to the right, is it what you intended? If so, take into account that excel has 256 columns.
If not, replace:
.SpecialCells(xlCellTypeLastCell).Offset(1, 0)
to:
.SpecialCells(xlCellTypeLastCell).EntireRow.Cells(1).Offset(1, 0)

combo

 

[SkipVought]

You would make out much better using ADO and the CopyFromRecordset method.

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}