Здравствуйте
помогите пожалуйста если множите люди добрые
Вот кусочек макроса записанного.
Код |
---|
Sub Ìàêðîñ1()
'
' Ìàêðîñ1 Ìàêðîñ
'
'
ActiveWorkbook.Queries("Otchet").Delete
ActiveWorkbook.Queries.Add Name:="Otchet", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Èñòî÷íèê = Csv.Document(File.Contents(""E:\Otchet.csv""),[Delimiter="","",Encoding=65001])," & Chr(13) & "" & Chr(10) & " #""Èçìåíåííûé òèï"" = Table.TransformColumnTypes(Èñòî÷íèê,{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type text}})," & Chr(13) & "" & Chr(10) & " #""Óäàëåííûå ñòîëáöû"" = Table.RemoveColumns(#""Èçìåíå" & _
"ííûé òèï"",{""Column1""})," & Chr(13) & "" & Chr(10) & " #""Ïîâûøåííûå çàãîëîâêè"" = Table.PromoteHeaders(#""Óäàëåííûå ñòîëáöû"")" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Ïîâûøåííûå çàãîëîâêè"""
Sheets.Add After:=ActiveSheet
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Otchet" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Otchet]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = False
.ListObject.DisplayName = ("Otchet")
.Refresh BackgroundQuery:=False
End With
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
ActiveSheet.ListObjects("Otchet").TableStyle = "TableStyleLight14"
Application.CommandBars("Workbook Queries").Visible = False
Range("E6").Select
Application.CommandBars("Workbook Queries").Visible = False
Range("D2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-2],C[-2])"
Columns("D:D").Select
Selection.Copy
Columns("E:E").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("D:D").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("D:D").Select
Selection.Cut
Range("Otchet[[#Headers],[Material]]").Select
ActiveWindow.SmallScroll Down:=39
Range("C56").Select
ActiveSheet.ListObjects("Otchet").Resize Range("$A$1:$D$55")
Range("C56").Select
ActiveWindow.SmallScroll Down:=-69
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("Otchet[#All]").Select
ActiveSheet.Range("Otchet[#Âñå]").RemoveDuplicates Columns:=Array(1, 2, 3, 4), _
Header:=xlYes
Range("Îò÷åò").Select
ActiveWorkbook.Worksheets("Ëèñò2").ListObjects("Otchet").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Ëèñò2").ListObjects("Otchet").Sort.SortFields.Add _
Key:=Range("Îò÷åò[Layer]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Ëèñò2").ListObjects("Îò÷åò").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("Otchet[[#Headers],[Ñòîëáåö1]]").Select
ActiveCell.FormulaR1C1 = "Êîë"
Range("B2").Select
ActiveWindow.SmallScroll Down:=-9
ActiveWorkbook.Queries("Otchet").Delete
End Sub
|
И вот место в котором приделало ошибку
Код |
---|
ActiveSheet.Range("Îò÷åò[#Âñå]").RemoveDuplicates Columns:=Array(1, 2, 3, 4), _
Header:=xlYes |