|
INAさん、度々おはようございます。
Excelは2000
windowsは95
です。
マクロブック テスト用.xls
テキストファイルの名前
【301在庫.txt】
【センター別在庫】
【棚在庫】
です。
行き詰まりっぱなしです(≧×≦)。
一応、登録したマクロも載せてみました。。
お時間がありましたら助けてください。
毎回ごめんなさい。
Sub Macro2()
'
' Macro2 Macro
' マクロ記録日 : 2003/10/23 ユーザー名 :
'
Workbooks.OpenText Filename:="D:\DATA\301在庫.txt", StartRow:=1, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter _
:=False, Tab:=True, Semicolon:=False, Comma:=True, Space:=False, _
Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), _
Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), _
Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array( _
18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), _
Array(25, 1), Array(26, 1), Array(27, 2))
Selection.End(xlDown).Select
Range("A1:AA673").Select
Range("A673").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.End(xlUp).Select
Rows("1:8").Select
Selection.Insert Shift:=xlDown
Range("D1:AA9").Select
Selection.NumberFormatLocal = "@"
Workbooks.Open Filename:="D:\DATA\サイズ一覧.xls"
Range("B2:W9").Select
Selection.Copy
ActiveWindow.WindowState = xlMinimized
With ActiveWindow
.Top = 4
.Left = 8.5
End With
Range("D2").Select
ActiveSheet.Paste
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Range("E10").Select
ActiveWindow.FreezePanes = True
With ActiveWindow
.Top = 62.5
.Left = 13
End With
Workbooks.OpenText Filename:="D:\DATA\センター別在庫.txt", StartRow:=1, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter _
:=False, Tab:=True, Semicolon:=False, Comma:=True, Space:=False, _
Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), _
Array(5, 1), Array(6, 1), Array(7, 1))
Selection.End(xlDown).Select
Range("A1:G9550").Select
Range("A9550").Activate
Application.CutCopyMode = False
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.End(xlUp).Select
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Workbooks.OpenText Filename:="D:\DATA\棚在庫.txt", StartRow:=1, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter _
:=False, Tab:=True, Semicolon:=False, Comma:=True, Space:=False, _
Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), _
Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 2))
Selection.End(xlDown).Select
Range("A1:H7809").Select
Range("A7809").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.End(xlUp).Select
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Columns("H:H").EntireColumn.AutoFit
Windows("テスト用.xls").Activate
Sheets("Sheet1").Select
With ActiveWindow
.Top = 178.75
.Left = 25.75
End With
Windows("301在庫.txt").Activate
Sheets("301在庫").Copy before:=Workbooks("テスト用.xls").Sheets(1)
Windows("301在庫.txt").Activate
With ActiveWindow
.Top = 59.5
.Left = -4.25
End With
ActiveWindow.Close
Windows("棚在庫.txt").Activate
Sheets("棚在庫").Copy before:=Workbooks("テスト用.xls").Sheets(2)
With ActiveWindow
.Top = 144.25
.Left = 18.25
End With
Windows("棚在庫.txt").Activate
ActiveWindow.Close
Windows("センター別在庫.txt").Activate
Sheets("センター別在庫").Copy before:=Workbooks("テスト用.xls").Sheets(3)
Windows("センター別在庫.txt").Activate
ActiveWindow.Close
With ActiveWindow
.Top = 4.75
.Left = 2.5
End With
Windows("サイズ一覧.xls").Activate
ActiveWindow.Close
Workbooks.OpenText Filename:="D:\DATA\301在庫.xls"
Windows("テスト用.xls").Activate
Sheets("301在庫").Copy before:=Workbooks("301在庫.xls").Sheets(1)
With ActiveWindow
.Top = 59.5
.Left = -4.25
End With
Windows("テスト用.xls").Activate
Sheets("棚在庫").Copy before:=Workbooks("301在庫.xls").Sheets(2)
With ActiveWindow
.Top = 144.25
.Left = 18.25
End With
Windows("テスト用.xls").Activate
Sheets("センター別在庫").Copy before:=Workbooks("301在庫.xls").Sheets(3)
With ActiveWindow
.Top = 4.75
.Left = 2.5
End With
Windows("301在庫.xls").Activate
ActiveWindow.Close
Windows("テスト用.xls").Activate
ActiveWindow.Close
End Sub
|
|