File input though some kind of windows interface

[Philipp Oberleitner]

Hi all at the moment i use the following code

Sub ShowOpenTickets()
Dim wks1 As Worksheet
Dim wks2 As Worksheet
Dim i As Long
Dim iAnz As Long
Const strWorkbook As String = "F:\Siemens\Auswertung.xls"
Const strSheet1 As String = "Auswertung"
Const strSheet2 As String = "Open Tickets - Solution"

Application.ScreenUpdating = False
Workbooks.Open strWorkbook

With ActiveWorkbook
Set wks1 = .Worksheets(strSheet1)
End With

On Error Resume Next
With ThisWorkbook
If .Worksheets(strSheet2) Is Nothing Then
..Worksheets.Add.Name = strSheet2
End If
On Error GoTo 0

Set wks2 = .Worksheets(strSheet2)
wks2.Cells.Clear

For i = 1 To wks1.Cells(Rows.Count, "D").End(xlUp).Row
Select Case wks1.Cells(i, "D").Value
Case "in work", "assigned", "Status", "new"
Select Case Left(wks1.Cells(i, "K").Value, 3)
Case "SOL", "Pro"
iAnz = iAnz + 1
wks2.Rows(iAnz).Value = wks1.Rows(i).Value
End Select
End Select
Next i
End With
ActiveWorkbook.Close
Application.ScreenUpdating = True
MsgBox "Es wurden " & iAnz & " offene Tickets übertragen"
End Sub

to bring data from one excel file into another one. But it works only with
conastant values like
Const strWorkbook As String = "F:\Siemens\Auswertung.xls"
Const strSheet1 As String = "Auswertung"
Const strSheet2 As String = "Open Tickets - Solution"

Is this possible to make some kind of interface so that the user can decide
which file to take, interface like one in windows where i can decide which
file to open for example.

Thanks alot in advance

 

[Jim Cone]

Philipp,

Take a look at the "GetOpenFileName" method.
It Displays the standard Open dialog box and gets a file name from the user without actually opening any files.

Regards,
Jim Cone
San Francisco, CA

 

[Tom Ogilvy]

Look in Excel help at the

Application.GetOpenfileName command. It shows the file open dialog and
returns the selected file as a string which you can assign to the variable
strWorkbook