Deleting a column from a different spreadsheet

[WelshyWizard]

Hi all,

I've got a spreadsheet that has a list of other spreadsheet names in column A. As shown below:

891001
901001
928001
988121
etc...

I want to write some code to look at each name and delete the same column (column M on sheet 2) from each spreadsheet. The part I am struggling with is this.
Can I delete a column from a spreadsheet without opening it? I suspect not, so my next question is, can I open and close a spreadsheet without the user being prompted to do anything like save changes etc...

Any help would be great,

Thanks.

Today is the tomorrow you worried about yesterday - and all is well.....

 

[SkipVought]

Hi,

'delete column n
sName = "SomeSheetName"
Worksheet(sName).Columns("N:N").Delete

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!}

 

[WelshyWizard]

Sorry,

I've just raelised that I've used the wrong terminiology. I meant workbook, not spreadsheet.

So each reference is actually an xls file.

Today is the tomorrow you worried about yesterday - and all is well.....

 

[SkipVought]

Workbooks must be open.

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!}

 

[sahmiele]

try this... the first for/each loads an array with the file names. make sure to change the pathname to be what your chosen folder's path name is.


Dim filearray()
pathname = "C:\Documents and Settings\"
i = 0
rowcounter = ActiveSheet.UsedRange.Rows.Count
ReDim filearray(rowcounter)
filerange = "a2:a" & rowcounter
For Each a In Range(filerange)
filearray(i) = pathname & a.Text & ".xls"
i = i + 1
Next a


For a = 0 To i
Workbooks.Open Filename:=filearray(a)
Columns("N:N").Select
Selection.Delete Shift:=xlToLeft
ActiveWorkbook.Save
ActiveWindow.Close
Next a