question about loop to copy content in folder to a worksheet

[ces005]

Hi,

I have a bunch of .xls files in a folder on my desktop (let's call it myFolder).

If I want to copy the contents of worksheet called "Template" in every .xls file in that folder (except for the workbook I am running the macro from) , any suggestions on how to do this? I am creating a macro in the Master file to run. I am concatenating the contents to the "ALLData" worksheet in the active workbook.

-Eileen


Set wbk = ThisWorkbook
Set sht = wbk.Sheets("ALLData")

Generate an array of file names containing .xls files from the folder
Delete the file name from this list where the file name matches the current active workbook

CurrentIndex = 0

' last index is the number of entries in the array
do until CurreintIndex < LastIndex
Workbooks.Open file name in array[current index]

' select rows 2 through the last row of the worksheet to be copied
' copy those rows
' paste those rows after the last row of sht (target sheet)
loop

 

[Norie]

I thought that might come up.

Again untested.

Set rngCopy = wsSrc.Range("A1").CurrentRegion

Set rngCopy = rngCopy.Offset(1).Resize(rngCopy.Rows.Count-1)
rngCopy.Copy wsDst.Range("A" & NextRow)

 

[ces005]

Also, how do I specify the logic to find the current directory?

strDirName = CurDir()

does not give the directory of the workbook I am executing the script from.

 

[ces005]

Wouldn't it be easier to retain the below logic

wsSrc.Range("A1").CurrentRegion.Copy wsDst.Range("A" & NextRow)

but delete the contents of NextRow (which would be the first row of the sheet being copied which I want to throw away)?

 

[Norie]

In the code I posted I simply specified the directory here.

strDirName = "C:\myFolder\"

Which was the directory you mentioned in the original post.

As to the other question, yes you might be able to do that but it's probably safer to not copy the 1st row in the first place.

 

[ces005]

If I add this to the end of the subroutine, why do I have a problem?

wsDst.Select
With Selection
.WrapText = False
End With

The logic at the beginning of the routine was this:

Set wbDst = ThisWorkbook
Set wsDst = wbDst.Sheets("AllData")

It seems to have a problem with the "wsDst.Select" line.

I am trying to disable wraparound for the AllData sheet after the copying operation is done.

 

[Norie]

Eh, I far as I recall I didn't post any code like this.

wsDst.Select

 

[ces005]

comment about extra code at the end

Hi,

I tried to add some extra at the code to disable word wrapping in the AllData worksheet. This is not your code.

-Eileen

 

[ces005]

Here is what I have now. the code after "End With" is not working. I am trying to select the "AllData" sheet and disable word wrap. Any advice?

    Set wbDst = ThisWorkbook
    Set wsDst = wbDst.Sheets("AllData")
    Set wsLog = wbDst.Sheets("Log")
    Set oSearch = Application.FileSearch

    wsLog.Cells.Clear
    wsDst.Cells.Clear
    
    With oSearch
           .
           .
           .
    End With

    wsDst.Select
    Cells.Select
    With Selection
        .WrapText = False
    End With

[/code]

 

[Norie]

Try this.

wsDst.Cells.WrapText = False

 

[ces005]

question about executing script from web posting

Hi,

Is it possible to execute an excel macro from an excel file posted on a web page? If I do this, what kind of path would I have to specify in place of

strDirName = "C:\myFolder\"

Let's say I am executing the script from an opened up file at http://<my url>. The macro was expecting the files to be copied to be located in a folder at strDirName. I don't know if I can avoid copying the files posted on the web to a local folder.

-Eileen