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 | 874 (656) | Silikon |
» | null reference exception na čudnem mestu C#Oddelek: Programiranje | 1303 (1240) | krho |
» | Makroji v exceluOddelek: Programska oprema | 2478 (2372) | smetko |
» | [Excel]Vrednost celice = ime dokumentaOddelek: Programska oprema | 4751 (4173) | veteran |
» | Excel vprašanjeOddelek: Programiranje | 1143 (1042) | matic |