Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting data from one xls file and input into another xls file | Excel Discussion (Misc queries) | |||
new windows for new excel file | Excel Discussion (Misc queries) | |||
Here is fix for Windows file problems | New Users to Excel | |||
Here is fix for Windows file problems | Excel Discussion (Misc queries) | |||
some kind of meta-way to refer to sheet and file names in Excel? | Excel Discussion (Misc queries) |