File input though some kind of windows interface
 

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

File input though some kind of windows interface
 

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

"Philipp Oberleitner" wrote in message ...
Hi all at the moment i use the following code
- snip -
to bring data from one excel file into another one. But it works only with
constant values...
- snip -
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

File input though some kind of windows interface
 

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

--
Regards,
Tom Ogilvy

"Philipp Oberleitner" wrote in message
...
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