Доброго времени суток! У меня есть excel файл в котором по запросу выполняется выборка данных из sql и макросом выполняется форматирование данных! могу ли я в query добавить еще один столбец данных! сам макрос и меет такой вид!
Sub WDR()
'On Error Resume Next
'Получаю дату с имени последнего листа
SecondDate = Mid(Sheets(Sheets.Count).Name, 15, 2) & "/" & Mid(Sheets(Sheets.Count).Name, 18, 2) & "/" & Mid(Sheets(Sheets.Count).Name, 21, 2)
FirstDate = Mid(Sheets(Sheets.Count).Name, 6, 2) & "/" & Mid(Sheets(Sheets.Count).Name, 9, 2) & "/" & Mid(Sheets(Sheets.Count).Name, 12, 2)
Sheets(Sheets.Count).Copy Before:=Sheets(1)
IntervalType = "ww"
Number = Sheets(Sheets.Count - 1).Index 'количество прибавляемых недель
'дата для новой недели
newWeekF = DateAdd(IntervalType, Number, Format(FirstDate, "mm/dd/yy;@"))
newWeekS = DateAdd(IntervalType, Number, Format(SecondDate, "mm/dd/yy;@"))
'необходима дата в этих ячейках для импорта данных
Sheets(1).Cells(1, "L").Value = newWeekF
Sheets(1).Cells(1, "M").Value = newWeekS
For i = 1 To 8
If Mid(newWeekF, i, 1) = "/" Then Mid(newWeekF, i, 1) = "."
If Mid(newWeekS, i, 1) = "/" Then Mid(newWeekS, i, 1) = "."
Next
'собственно имя нового листа
Sheets(1).Name = "Week " & newWeekF & "-" & newWeekS
ActiveWorkbook.RefreshAll
'приводим к нужному виду
Columns("C:C").Select
Selection.NumberFormat = "@"
Columns("D:D").Copy
Columns("C:C").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Columns("B:B").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


t = 3
Do
Sheets(1).Cells(t, 2).Value = Left(Sheets(1).Cells(t, 2).Value, 4)
Sheets(1).Cells(t, 3).Value = Right(Sheets(1).Cells(t, 3).Value, 5)
t = t + 1
Loop While Sheets(1).Cells(t, 4).Value <> ""
Columns("D:D").Select
Selection.EntireColumn.Hidden = True
'выравнивание по центру
'Columns("E:E").Select
'Application.CutCopyMode = False
Selection.Cut
'Columns("H:H").Select
'Selection.Insert Shift:=xlToRight
q = 3
Do
Sheets(1).Cells(q, "K").Value = Sheets(1).Cells(q, "I").Value + Sheets(1).Cells(q, "J").Value
q = q + 1
Loop While Sheets(1).Cells(q, "B").Value <> ""
Sheets(1).Cells(q + 1, "I").Formula = "=SUM(" & Cells(3, "I").Address & ":" & Cells(q, "I").Address & ")"
Sheets(1).Cells(q + 1, "J").Formula = "=SUM(" & Cells(3, "J").Address & ":" & Cells(q, "J").Address & ")"
Sheets(1).Cells(q + 1, "K").Formula = "=SUM(" & Cells(3, "K").Address & ":" & Cells(q, "K").Address & ")"
Sheets(1).Cells(3, "A").Select
ActiveWindow.FreezePanes = True
Rows(2).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.Font.Bold = True
End With
Range(Cells(3, 2), Cells(q - 1, "K")).Select
Selection.Sort Key1:=Range("C3"), Order1:=xlAscending, Key2:=Range("B3") _
, Order2:=xlAscending, Key3:=Range("F3"), Order3:=xlAscending, Header:= _
xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers, DataOption2:=xlSortNormal, DataOption3 _
:=xlSortNormal
Columns("H:H").Select
Selection.ColumnWidth = 10
Range(Cells(3, "H"), Cells(q - 1, "K")).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("A:C").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With