pcc
Well-known Member
- Joined
- Jan 21, 2003
- Messages
- 1,357
I have a set of workbooks in a common folder. There are many of them, and they are large files. Before processing all of the data (I need to get all data into an Access database), I want to check that all of the header rows are the same (they have been set up by someone else, and I can't rely on them having done it properly!) I don't want to have to open each file up in turn as this will take ages due to large file szes.
I have the following code that I have used before to do the same process, by reading CSV files, and it works OK
this yields (eg of what's written to the 'Log' sheet)
"38797.46711","U509262","PAG001","Pegasus","PEL","REL","Budget","£PEL","£REL"
for which I can then use Data>Text to columns to sort out. However, the files of interest this time are regular Excel .xls files, and I get this (eg)
ÐÏࡱ
which isn't much use!!
Does anyone have code that can quickly read in just the first row from each xls file so that I can check the headers?
All suggestions welcomed, thanks
I have the following code that I have used before to do the same process, by reading CSV files, and it works OK
Code:
Sub demo()
row_count = 2 ' first line to write data to on log sheet
Sheets("Files").Activate
lr = [a1].CurrentRegion.Rows.Count
For drow = 1 To lr
wbname = Cells(drow, 1).Value ' name of workbook from which header is to be read
wbname = path & "\" & wbname ' define the full opath and filename
Open wbname For Input Access Read As #ff
Line Input #1, mystr ' Read line of data.
ThisWorkbook.Sheets("Log").Cells(row_count, 1).Value = mystr
row_count = row_count + 1
Close #ff
Application.DisplayAlerts = True
Next drow
End Sub
this yields (eg of what's written to the 'Log' sheet)
"38797.46711","U509262","PAG001","Pegasus","PEL","REL","Budget","£PEL","£REL"
for which I can then use Data>Text to columns to sort out. However, the files of interest this time are regular Excel .xls files, and I get this (eg)
ÐÏࡱ
which isn't much use!!
Does anyone have code that can quickly read in just the first row from each xls file so that I can check the headers?
All suggestions welcomed, thanks