hab ein Problem, und zwar ich habe ca. 50 Dateien, jetzt möchte ich in jede Datei (mit Tabellen) neue Spalten einfügen, wie kann ich das mit VBA vereinfachen?
gruß
Rich.
Sub start_insert_cols()
'Rolf Beißner.hf.14.04.05
Const VerzDefault As Variant = "C:\"
Dim verz As String
verz = Ordner_def(VerzDefault)
ChDir verz
InsertCols = InputBox("Bitte die Spalten eingeben", "Spalteneingabe", "C:D")
Application.ScreenUpdating = False
WorkFileList (verz)
End Sub
Sub WorkFileList(folderspec)
On Error Resume Next
Dim fs As Object, f As Object, fc As Object, fl As Object
Dim s As Object
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.Files
For Each fl In fc
If fl.Type = "Microsoft Excel-Arbeitsblatt" Then
Workbooks.Open fl.Name
For Each s In Sheets
s.Columns(InsertCols).Insert Shift:=xlToRight
Next
Workbooks(fl.Name).Close True
End If
Next
End Sub
Function Ordner_def(defaultwert As Variant) As String
Dim objFolderItem As Object, strPath As String, objShell As Object
Dim objFolder As Object
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.BrowseForFolder(0&, "Ordner auswählen...", 0&, defaultwert)
If objFolder Is Nothing Then End
Set objFolderItem = objFolder.Self
strPath = objFolderItem.Path
Ordner_def = strPath
End Function