ich habe vor geraumer Zeit folgenden Code aus dem Forum bekommen,
ich würde hier gerne noch eine weitere einschränkung machen und zwar sollen nur die Tabellenblätter die Visible = xlSheetVisible sind, gespeichert werden.
******************************************************************
Option Explicit
Private Declare Function MoveWindow Lib "user32.dll" ( _
ByVal hWnd As Long, _
ByVal X As Long, _
ByVal Y As Long, _
ByVal nWidth As Long, _
ByVal nHeight As Long, _
ByVal bRepaint As Long) As Long
Private Declare Function GetSystemMetrics Lib "user32.dll" ( _
ByVal nIndex As Long) As Long
Private Declare Function GetWindowRect Lib "user32.dll" ( _
ByVal hWnd As Long, _
ByRef lpRect As RECT) As Long
Private Declare Function SHBrowseForFolder Lib "shell32" ( _
ByRef lpbi As InfoT) As Long
Private Declare Function CoTaskMemFree Lib "ole32" ( _
ByVal hMem As Long) As Long
Private Declare Function lstrcat Lib "kernel32" Alias "lstrcatA" ( _
ByVal lpStr1 As String, _
ByVal lpStr2 As String) As Long
Private Declare Function SHGetPathFromIDList Lib "shell32" ( _
ByVal pList As Long, _
ByVal lpBuffer As String) As Long
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
ByVal lpClassname As String, _
ByVal lpWindowName As String) As Long
Private Declare Function SendMessage Lib "user32.dll" Alias "SendMessageA" ( _
ByVal hWnd As Long, _
ByVal Msg As Long, _
ByRef wParam As Any, _
ByRef lParam As Any) As Long
Private Type InfoT
hWnd As Long
Root As Long
DisplayName As Long
Title As Long
Flags As Long
FName As Long
lParam As Long
Image As Long
End Type
Private Type RECT
Left As Long
Top As Long
Right As Long
Bottom As Long
End Type
Private Const BIF_RETURNONLYFSDIRS = &H1
Private Const BIF_DONTGOBELOWDOMAIN = &H2
Private Const BIF_STATUSTEXT = &H4
Private Const BIF_RETURNFSANCESTORS = &H8
Private Const BIF_EDITBOX = &H10
Private Const BIF_VALIDATE = &H20
Private Const BIF_NEWDIALOGSTYLE = &H40
Private Const BIF_BROWSEINCLUDEURLS = &H80
Private Const BIF_BROWSEFORCOMPUTER = &H1000
Private Const BIF_BROWSEFORPRINTER = &H2000
Private Const BIF_BROWSEINCLUDEFILES = &H4000
Private Const BIF_SHAREABLE = &H8000
Private Const SM_CXFULLSCREEN = &H10
Private Const SM_CYFULLSCREEN = &H11
Private Const BFFM_SETSELECTION = &H466
Private Const BFFM_INITIALIZED = &H1
Private s_BrowseInitDir As String
Public Function fncGetFolder( _
Optional ByVal sMsg As String = "Bitte wählen Sie ein Verzeichnis ", _
Optional ByVal lFlag As Long = BIF_RETURNONLYFSDIRS, _
Optional ByVal sPath As String = "C:\") As String
Dim xl As InfoT, IDList As Long, RVal As Long, FolderName As String
s_BrowseInitDir = sPath
With xl
.hWnd = FindWindow("XLMAIN", vbNullString)
.Root = 0
.Title = lstrcat(sMsg, "")
.Flags = lFlag
.FName = FncCallback(AddressOf BrowseCallback)
End With
IDList = SHBrowseForFolder(xl)
If IDList 0 Then
FolderName = Space(256)
RVal = SHGetPathFromIDList(IDList, FolderName)
CoTaskMemFree (IDList)
FolderName = Trim$(FolderName)
FolderName = Left$(FolderName, Len(FolderName) - 1)
End If
fncGetFolder = FolderName
End Function
Private Function BrowseCallback( _
ByVal hWnd As Long, _
ByVal uMsg As Long, _
ByVal wParam As Long, _
ByVal lParam As Long) As Long
If uMsg = BFFM_INITIALIZED Then
Call SendMessage(hWnd, BFFM_SETSELECTION, ByVal 1&, ByVal s_BrowseInitDir)
Call prcCenterDialog(hWnd)
End If
BrowseCallback = 0
End Function
Private Function FncCallback(ByVal nParam As Long) As Long
FncCallback = nParam
End Function
Private Sub prcCenterDialog(ByVal hWnd As Long)
Dim WinRect As RECT, ScrWidth As Integer, ScrHeight As Integer
Dim DlgWidth As Integer, DlgHeight As Integer
GetWindowRect hWnd, WinRect
DlgWidth = WinRect.Right - WinRect.Left
DlgHeight = WinRect.Bottom - WinRect.Top
ScrWidth = GetSystemMetrics(SM_CXFULLSCREEN)
ScrHeight = GetSystemMetrics(SM_CYFULLSCREEN)
MoveWindow hWnd, (ScrWidth - DlgWidth) / 2, _
(ScrHeight - DlgHeight) / 2, DlgWidth, DlgHeight, 1
End Sub
Public Sub speichern_ohne_Makros()
Dim Name As String
Dim anwendung As Integer
Dim strFolder As String, strFilename As String
Dim objVBC As Object
strFolder = Trim$(fncGetFolder())
If strFolder "" Then
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
If Right$(strFolder, 1) "\" Then strFolder = strFolder & "\"
strFilename = Worksheets("coordinates").Cells(1, 7) & "a_" & Worksheets("coordinates").Cells(1, 1).Text & ".xls"
'******* Anweisung für die Prozedur und Warnung *******'
Name = Worksheets("coordinates").Cells(1, 7) & "a_" & Worksheets("coordinates").Cells(1, 1).Text & ".xls"
anwendung = MsgBox("Wenn sie jetzt auf Yes drücken dann, " & Chr(13) & " werden gegebennenfalls vorhandenen Daten mit dem Namen:" & Chr(13) & Chr(13) & "' " & Name & " '" & Chr(13) & Chr(13) & "im Verzeichniss" & Chr(13) & Chr(13) & strFolder & Chr(13) & Chr(13) & " überschrieben." & Chr(13) & Chr(13) & " Wenn sie auf Nein Drücken wird die Prozedur ohne speichern abgebrochen", vbYesNo + vbInformation, "Anweisung_0003")
If anwendung = 6 Then
strFolder = strFolder & strFilename
ThisWorkbook.SaveCopyAs strFolder
Workbooks.Open strFolder
With Workbooks(strFilename).VBProject
For Each objVBC In .VBComponents
Select Case objVBC.Type
Case 1, 2, 3
.VBComponents.Remove .VBComponents(objVBC.Name)
Case 100
With objVBC.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next
End With
Workbooks(strFilename).Close SaveChanges:=True
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End If
End If
End Sub
************************************
Gruß
wuntschi