Frage von Jolker89, 34

Excel VBA mit commandbutton Datei öffnen welche den selben Namen wie eine Zelle hat?

Hi

ich möchte in eine Zelle einen Namen schreiben und mit einem CommandButton in einem bestimmten Ordner eine Datei öffnen welche den selben Namen hat, den ich in die Zelle eingetragen habe.

kann mir da bitte jemand mit einem Code weiterhelfen??

Grüße

Björn

Antwort
von PWolff, 28
Sub CommandButton1_Click()
Dim Dateiname As String
Dateiname = Bezugszelle.Value
On Error Resume Next ' falls beim Auswerten des Dir-Befehls etwas schiefgeht
If Dir(Ordnername + "\" + Dateiname) = "" Then Exit Sub
If Err.Number <> 0 Then Exit Sub
On Error GoTo 0 'Fehlerabfangen beenden

Open Ordnername + "\" + Dateiname For Input As #1
... etc
Close #1
End Sub

bzw.

Sub CommandButton2_Click()
Dim Dateiname As String
Dateiname = Bezugszelle.Value
On Error Resume Next ' falls beim Auswerten des Dir-Befehls etwas schiefgeht
Dir Ordnername + "\" + Dateiname 'Hack: Prüfen, ob der Dateiname gültig ist
If Err.Number <> 0 Then Exit Sub
On Error GoTo 0 'Fehlerabfangen beenden

Open Ordnername + "\" + Dateiname For Output As #1 'oder Append oder Binary
... etc
Close #1
End Sub
Kommentar von Jolker89 ,

Vielen Dank,

funktioniert einwandfrei


wie kann ich es denn noch machen damit mir das pdf dokument, das ich mir anzeigen lasse, sich im Vordergrund öffnet??

Kommentar von PWolff ,

Füg ein Modul hinzu (falls das Projekt noch keins hat)

Füg in das Modul folgenden Code ein:


' ShowWindow() Commands
Public Const SW_HIDE = 0
Public Const SW_SHOWNORMAL = 1
Public Const SW_NORMAL = 1
Public Const SW_SHOWMINIMIZED = 2
Public Const SW_SHOWMAXIMIZED = 3
Public Const SW_MAXIMIZE = 3
Public Const SW_SHOWNOACTIVATE = 4
Public Const SW_SHOW = 5
Public Const SW_MINIMIZE = 6
Public Const SW_SHOWMINNOACTIVE = 7
Public Const SW_SHOWNA = 8
Public Const SW_RESTORE = 9
Public Const SW_SHOWDEFAULT = 10
Public Const SW_MAX = 10
'SW_HIDE 'Hides the window and activates the executable file.
'SW_MAXIMIZE 'Maximizes the window.
'SW_MINIMIZE 'Minimizes the window. The next top-level window in the Z-order is activated.
'SW_RESTORE 'Activates the window even if it is hidden or minimized
'SW_SHOW 'Activates the window and displays it in its original size and at its original position.
'SW_SHOWMAXIMIZED 'Activates the window. The window is displayed as maximized.
'SW_SHOWMINIMIZED 'Activates the window. The window is displayed as minimized.
'SW_SHOWMINNOACTIVE 'Activates the window as minimized. The active window retains the focus.
'SW_SHOWNA 'Activates the window in its current state but the active window retains the focus.
'SW_SHOWNOACTIVATE 'Displays the window in its most recent size and in its most recent position. The active window retains the focus.
'SW_SHOWNORMAL 'Displays the window in its original size and at its original position.
' error values for ShellExecute() beyond the regular WinExec() codes
Public Const SE_ERR_SHARE = 26
Public Const SE_ERR_ASSOCINCOMPLETE = 27
Public Const SE_ERR_DDETIMEOUT = 28
Public Const SE_ERR_DDEFAIL = 29
Public Const SE_ERR_DDEBUSY = 30
Public Const SE_ERR_NOASSOC = 31

Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
'ShellExecute-Rückgabewerte: http://support.microsoft.com/?scid=kb%3Ben-us%3B238245&x=13&y=12 ' Copyright ©2007 by Günter Pütz Software
'Rückgabewert von ShellExecute: Handle der aufgerufenen Anwendung, falls erfolgreich. Im Fehlerfall ein Wert <= 32.
'Fehlerwerte:
' 0 System was out of memory, executable file was corrupt, or relocations were invalid.
' 2 File was not found.
' 3 Path was not found.
' 5 Attempt was made to dynamically link to a task, or there was a sharing or network-protection error.
' 6 Library required separate data segments for each task.
' 8 There was insufficient memory to start the application.
' 10 Windows version was incorrect.
' 11 Executable file was invalid. Either it was not a Windows application, or there was an error in the .exe image.
' 12 Application was designed for a different operating system.
' 13 Application was designed for MS-DOS 4.0.
' 14 Type of executable file was unknown.
' 15 Attempt was made to load a real-mode application (developed for an earlier version of Windows).
' 16 Attempt was made to load a second instance of an executable file containing multiple data segments that were not marked read-only.
' 19 Attempt was made to load a compressed executable file. The file must be decompressed before it can be loaded.
' 20 Dynamic-link library (DLL) file was invalid. One of the DLLs required to run this application was corrupt.
' 21 Application requires Microsoft Windows 32-bit extensions.
'Erfahrungsgemäß weitere Werte:
' 31 Dateiendung nicht zugeordnet

Und in die betreffende Prozedur

        Dim Result As Long
Result = ShellExecute(Me.hwnd, "Open", Dateiname, "", vbNullString, SW_SHOWNORMAL)

Falls Result <= 32 ist, ist der Aufruf fehlgeschlagen, falls Result > 32 ist, gelungen.

Keine passende Antwort gefunden?

Fragen Sie die Community

Weitere Fragen mit Antworten