jeroenkoning
Nieuwe gebruiker
- Lid geworden
- 1 jul 2015
- Berichten
- 1
Hallo Helpers,
Dit is voor mij de eerste ervaring met VBA.
Ik heb een macro gemaakt met record button.
Nu wil ik in het begin zelf het bestand kunnen selecteren in plaats van de huidige situatie.
Sub box()
'
' box Macro
'
' Keyboard Shortcut: Ctrl+t
'
Workbooks.OpenText Filename:= _
"H:\Data\My Documents\data Sokkia\test d.3dl", Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array( _
3, 1), Array(4, 1)), TrailingMinusNumbers:=True
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Columns("C:C").Select
Columns("D").Select
Selection.Delete Shift:=xlToLeft
Columns("H:L").Select
Selection.Delete Shift:=xlToLeft
Columns("H:H").Select
Selection.ClearContents
Columns("L:AE").Select
Selection.ClearContents
ActiveWindow.ScrollColumn = 1
Range("A1:F1").Select
With Selection.Font
.Color = -16776961
.TintAndShade = 0
End With
Range("A1:M19").Select
ActiveWindow.SmallScroll Down:=-45
Columns("A:Q").Select
With Selection.Font
.Name = "Arial"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Rows("1:1").Select
With Selection.Font
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.TintAndShade = 0
End With
Selection.Font.Bold = True
Range("M7").Select
ActiveWindow.SmallScroll Down:=-9
Columns("D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
Columns("G:G").EntireColumn.AutoFit
Columns("I:I").EntireColumn.AutoFit
Columns("J:J").EntireColumn.AutoFit
Columns("K:K").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("A:A").Select
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("N4").Select
ActiveWindow.SmallScroll Down:=-12
End Sub
Gr. Jeroen
Dit is voor mij de eerste ervaring met VBA.
Ik heb een macro gemaakt met record button.
Nu wil ik in het begin zelf het bestand kunnen selecteren in plaats van de huidige situatie.
Sub box()
'
' box Macro
'
' Keyboard Shortcut: Ctrl+t
'
Workbooks.OpenText Filename:= _
"H:\Data\My Documents\data Sokkia\test d.3dl", Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array( _
3, 1), Array(4, 1)), TrailingMinusNumbers:=True
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Columns("C:C").Select
Columns("D").Select
Selection.Delete Shift:=xlToLeft
Columns("H:L").Select
Selection.Delete Shift:=xlToLeft
Columns("H:H").Select
Selection.ClearContents
Columns("L:AE").Select
Selection.ClearContents
ActiveWindow.ScrollColumn = 1
Range("A1:F1").Select
With Selection.Font
.Color = -16776961
.TintAndShade = 0
End With
Range("A1:M19").Select
ActiveWindow.SmallScroll Down:=-45
Columns("A:Q").Select
With Selection.Font
.Name = "Arial"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Rows("1:1").Select
With Selection.Font
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.TintAndShade = 0
End With
Selection.Font.Bold = True
Range("M7").Select
ActiveWindow.SmallScroll Down:=-9
Columns("D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
Columns("G:G").EntireColumn.AutoFit
Columns("I:I").EntireColumn.AutoFit
Columns("J:J").EntireColumn.AutoFit
Columns("K:K").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("A:A").Select
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("N4").Select
ActiveWindow.SmallScroll Down:=-12
End Sub
Gr. Jeroen