-1

Possible Duplicate:
Excel - VBA Question. Need to access data from all excel files in a directory without opening the files

So I've been trying to put together the code people have been sending me and this is what I've got so far...

Dim xcell As Range
Dim ycell As Range
Dim sheetname As String

Dim wbList() As String, wbCount As Integer, i As Integer
Dim wbname As String


    FolderName = "\\Drcs8570168\shasad\Test"

    wbname = Dir(FolderName & "\" & "*.xls")

    While wbname <> ""

Set ycell = Range("a5", "h5")
Set xcell = Range("a2", "h2")
sheetname = "loging form"

ycell.Formula = "=" & "'" & FolderName & "\[" & wbname & "]" _
& sheetname & "'!" & xcell.Address


    Wend


End Sub

Since I'm not that familiar with this type of code I didn't change much from what people supplied me but I'm hoping it makes some sense together. The problem seems to be that the loop won't stop and it isn't outputting anything either.

Community
  • 1
  • 1
user960358
  • 295
  • 3
  • 6
  • 14
  • Sorry, made a mistake. It does output 3 lines(only 3 files in the directory) but they're all from the same file, so it looks like it's not moving down the list of files... – user960358 Sep 23 '11 at 18:52

1 Answers1

1

I'm not going to provide code, as you should work this out yourself in order to learn. (If you're looking for someone to write the entire code for you, you're at the wrong site - you need to try somewhere like Rentacoder.)

This should get you started, though...

wbList() is an array of strings. It should be where the return value of Dir() is assigned.

For each element in wbList you assign the string to wbName. That's what wbCount and i are declared for - wbCount would be the number of strings in wbList, and i would be the current index as you iterate through the array.

Ken White
  • 123,280
  • 14
  • 225
  • 444