Forum » Programiranje » Excel macro - kako ga preurediti?
Excel macro - kako ga preurediti?
egomez ::
Makrojev v excelu nisem nikoli uporabljal. Včeraj pa sem hotel narediti enega, ki bi mi uvozil csv datoteko in potem opreuredi podatke iz nje. Problem se pojavi, ker ne vem, kako bi naredil, da me vpraša iz katere *.csv datoteke naj importira podatke. Za točno določeno datoteko zgleda koda makroja tako:
Sub Makro1()
'
'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;N:\meritve\PRINT_01.CSV", _
Destination:=Range("A1"))
.Name = "PRINT_01"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 852
.TextFileStartRow = 3
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1)
.TextFileDecimalSeparator = "."
.TextFileThousandsSeparator = "'"
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll Down:=48
.
.tukaj sem zbrisal klobasasto kodo ker smatram, da ni ključna za rešitev problema.
.
ActiveWindow.ScrollRow = 1
Columns("A:A").EntireColumn.AutoFit
Range("E4").Select
End Sub
Ker se z Visual Basicom nisem nikoli ukvarjal vas prosim, če mi lahko kdo svetuje, kako spremeniti kodo, da me bo makro vprašal, katero datoteko hočem importirati.
Sub Makro1()
'
'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;N:\meritve\PRINT_01.CSV", _
Destination:=Range("A1"))
.Name = "PRINT_01"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 852
.TextFileStartRow = 3
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1)
.TextFileDecimalSeparator = "."
.TextFileThousandsSeparator = "'"
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll Down:=48
.
.tukaj sem zbrisal klobasasto kodo ker smatram, da ni ključna za rešitev problema.
.
ActiveWindow.ScrollRow = 1
Columns("A:A").EntireColumn.AutoFit
Range("E4").Select
End Sub
Ker se z Visual Basicom nisem nikoli ukvarjal vas prosim, če mi lahko kdo svetuje, kako spremeniti kodo, da me bo makro vprašal, katero datoteko hočem importirati.
- spremenil: egomez ()
nevone ::
Neki tazga:
o+ nevone
Sub Makro1()
'
'
direktorij$ = "N:\meritve"
ChDir direktorij$
filetoopen$ = Application.GetOpenFilename("CSV Files (*.csv), *.csv", , "Open file")
If filetoopen$ = "False" Then Exit Sub
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" + filetoopen$, _
Destination:=Range("A1"))
.Name = filename(filetoopen$)
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
....
....
End Sub
Function filename(datoteka As String) As String
cx = Len(datoteka)
If (InStr(datoteka, "\") <> 0) Or (InStr(datoteka, "\") <> 0) Then
While (Mid$(datoteka, cx, 1) <> "\") And (Mid$(datoteka, cx, 1) <> "/")
cx = cx - 1
Wend
x1$ = Mid$(datoteka, cx + 1)
cx = Len(x1$)
If (InStr(x1$, ".") <> 0) Then
While (Mid$(x1$, cx, 1) <> ".")
cx = cx - 1
Wend
filename = Mid$(x1$, 1, cx - 1)
Else
filename = x1$
End If
Else
filename = datoteka
End If
End Function
o+ nevone
Either we will eat the Space or Space will eat us.
Vredno ogleda ...
| Tema | Ogledi | Zadnje sporočilo | |
|---|---|---|---|
| Tema | Ogledi | Zadnje sporočilo | |
| » | rabib vba kodo za shranjevanje v bin (iz excella)Oddelek: Programiranje | 1005 (787) | Silikon |
| » | null reference exception na čudnem mestu C#Oddelek: Programiranje | 1491 (1428) | krho |
| » | Makroji v exceluOddelek: Programska oprema | 2762 (2656) | smetko |
| » | [Excel]Vrednost celice = ime dokumentaOddelek: Programska oprema | 5015 (4437) | veteran |
| » | Excel vprašanjeOddelek: Programiranje | 1275 (1174) | matic |