Working with 1000+ excel files to create a shape file


I have downloaded data (wind speed) for india for a state using NSRDB viewer which gave me 1000+ excel files .
Each files has daily wind data for a year for a point location.

Need help on creating a single excel file using 1000+ files which should have point coordinates(Lat & Lon) and average wind speed/year for that point location . I will use that file to import into arcmap to create wind map.

I am assuming that using macros we can achieve this , not sure if that is the right way ?

Unfortunately we don't have the resources to support Microsoft Excel. A quick search turned up an article that may be of use:

-- NSRDB Team

1. Use DOS to get a list of all the files. See:
2. Paste the list of files on a worksheet - name the worksheet "List of Files"
3. Create a loop that goes through the file list and pulls the data out.

Here's the sort of VBA code you'd need to do this

Sub Combine()

Dim i As Long
Dim j As Long
Dim k As Long
Dim WBK As Workbook
Dim Row_x As Long
Dim Col_x As Long
Dim File_x As Variant
Dim Data As Variant

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

j = 1

for i = 1 to 1000

'Identify the file name from your list of file names
File_x = sheets("List of Files").cells(i,1)

if File_x = "" then
exit for
end if

File_x = Filepath & File_x 'Filepath = C:\Users\ etc.

'Open an individual wind file
Workbooks.Open Filename:=File_x

'Copy the wind data from the Sheet and column it's on... This example is for column A
Data = sheets("Sheet1").range("A1:A9000")

'Target your main file

'Paste the data on Column j
Sheets("All Wind Data").Range(cells(1,j),cells(9000,j) = Data

'Increment up your destination column
j = j + 1

'Close the individual site file
Workbooks.Close Filename:=File_x

next i

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True

end sub