Private Sub Workbook_Open()
e = GetSetting("MyApp", "Startup", "MyPath", "c:\")
e = e & "BFМФЦ.txt"
Workbooks.OpenText Filename:=e, Origin:=1251 _
, StartRow:=2, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), Array( _
50, 2), Array(55, 2), Array(59, 2), Array(67, 1), Array(79, 1), Array(87, 1), Array(98, 2), _
Array(109, 2), Array(113, 1), Array(134, 1)), DecimalSeparator:=".", _
ThousandsSeparator:=",", TrailingMinusNumbers:=True
''''''''''''
Columns("A:A").ColumnWidth = 42.78
Columns("C:C").ColumnWidth = 4.29
Columns("D:D").ColumnWidth = 10
Columns("E:E").ColumnWidth = 11.78
Columns("F:F").ColumnWidth = 8.86
Columns("G:G").ColumnWidth = 13.43
Columns("H:H").ColumnWidth = 8.14
Columns("I:I").ColumnWidth = 8.71
Columns("J:J").ColumnWidth = 11
Columns("K:K").ColumnWidth = 12.29
Columns("L:L").ColumnWidth = 5.86
Columns("M:M").ColumnWidth = 7.43
Columns("N:N").ColumnWidth = 7.43
Set tbl = ActiveCell.CurrentRegion ' отыскиваем последнюю заполненную строку
n = tbl.Rows.Count
Range(Cells(1, 5), Cells(n, 5)).Select
Selection.NumberFormat = "0.00000"
Range("F:F,G:G,J:J,K:K").Select
Selection.NumberFormat = "0.00"
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.LeftMargin = Application.InchesToPoints(0.65)
.RightMargin = Application.InchesToPoints(0.196)
.BottomMargin = Application.InchesToPoints(0.19)
.TopMargin = Application.InchesToPoints(1.09)
.Zoom = False
End With
Range(Cells(1, 1), Cells(n, 13)).Select
With Selection
.Font.Name = "Times New Roman"
.Font.Size = 10
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlInsideVertical).Weight = xlThin
.Borders(xlInsideHorizontal).Weight = xlThin
End With
Open e For Input As #1
Line Input #1, strok
''''''''''''''''''''''''''
dat = Mid(strok, 1, 10)
org = Mid(strok, 12, 40)
If Mid(org, 68, 12) = "Курчатовский" Then
org = "ООО УО " & "Курчатовский"
End If
dezadr = Mid(strok, 53, 80)
dezinn = Mid(strok, 134, 10)
Orgname = Mid(strok, 155, 100)
OrgAdr = Mid(strok, 256, 80)
orginn = Mid(strok, 337, 10)
DezFio = Mid(strok, 358, 40) '
dezbuchg = Mid(strok, 399, 40)
kpp = Mid(strok, 440, 9)
Dezkpp = Mid(strok, 450, 9)
num = Mid(strok, 460, 4)
DezBank = Mid(strok, 469, 80)
Sum = Mid(strok, 550, 13)
dezbik = Mid(strok, 564, 9) '
Chet = Mid(strok, 575, 20) '
ks = Mid(strok, 596, 20) ' кор счет ДЕЗ
orgbank = Mid(strok, 617, 80) ' организации
orgbik = Mid(strok, 698, 9) '
orgrs = Mid(strok, 709, 20) '
orgks = Mid(strok, 730, 20) '
dogNum = Mid(strok, 751, 16) '
codplat = Mid(strok, 768, 10) '
monthNach = Mid(strok, 779, 2)
YearNach = Mid(strok, 782, 2)
SumPropis = Mid(strok, 785, 80)
OrgOKOHX = Mid(strok, 866, 6)
OrgOKPO = Mid(strok, 873, 10)
Orgtel = Mid(strok, 884, 15)
trschet = Mid(strok, 900, 20)
Ls = Mid(strok, 921, 16)
Deztel = Mid(strok, 938, 15)
adrPom = Mid(strok, 954, 30)
datdog = Mid(strok, 985, 10)
dezFioRp = Mid(strok, 996, 40)
OrgFioRp = Mid(strok, 1037, 40)
bossRp = Mid(strok, 1078, 30)
BOSS = Mid(strok, 1109, 30)
Fio = Mid(strok, 1140, 30)
naznpl = Mid(strok, 1171, 100)
Close #1
Select Case monthNach
Case "01"
mes = "январь"
Case "02"
mes = "февраль"
Case "03"
mes = "март"
Case "04"
mes = "апрель"
Case "05"
mes = "май"
Case "06"
mes = "июнь"
Case "07"
mes = "июль"
Case "08"
mes = "август"
Case "09"
mes = "сентябрь"
Case "10"
mes = "октябрь"
Case "11"
mes = "ноябрь"
Case "12"
mes = "декабрь"
End Select
Range(Cells(1, , Cells(n, ).Select
With Selection
.HorizontalAlignment = xlRight
End With
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Наименование товара(описание выполненных работ, оказанных услуг)"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Код вида товара"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Код"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Условное обозначе-ние (нацио-нальное)" 'Кол-во в месяц
Range("E1").Select
ActiveCell.FormulaR1C1 = "Количество (объем)"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Цена (тариф) за единицу измерения"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Стоимость товаров (работ, услуг), имущественных прав без налога -всего"
Range("H1").Select
ActiveCell.FormulaR1C1 = "В том числе сумма акциза"
Range("I1").Select
ActiveCell.FormulaR1C1 = "Налоговая ставка"
Range("J1").Select
ActiveCell.FormulaR1C1 = "Сумма налога, предъявляе-мая покупателю"
Range("K1").Select
ActiveCell.FormulaR1C1 = "Стоимость товаров(работ, услуг), имуществен-ных прав с налогом -всего"
Range("L1").Select
ActiveCell.FormulaR1C1 = "11"
Range("M1").Select
ActiveCell.FormulaR1C1 = "Краткое наименование"
Range("N1").Select
ActiveCell.FormulaR1C1 = "Регистрационный номер таможенной дек-ларации"
Range("A1").Select
Selection.Subtotal GroupBy:=12, Function:=xlSum, TotalList:=Array(7, 10, 11), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Selection.ClearOutline
Rows("2:2").Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Rows("6:6").Select
Selection.RowHeight = 18
Range("A1:A6,B1:B6,E1:E6,F1:F6,G1:G6,H1:H6,I1:I6,J1:J6,K1:K6,N1:N6").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.WrapText = True
.MergeCells = True
.Font.Name = "Times New Roman"
.Font.Size = 9.5
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlEdgeRight).Weight = xlThin
End With
Range("D1").Clear
Range("C3").Select
ActiveCell.FormulaR1C1 = "Код"
Range("D3").Select
ActiveCell.FormulaR1C1 = "Условное обозначе-ние (нацио-нальное)"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Единица измерения"
Range("M1").Clear
Range("L3").Select
ActiveCell.FormulaR1C1 = "Цифровой код"
Range("M3").Select
ActiveCell.FormulaR1C1 = "Краткое наименование"
Range("L1").Select
ActiveCell.FormulaR1C1 = "Страна происхождения товара"
Range("C1:D2,L1:M2,C3:C6,D3:D6,L3:L6,M3:M6").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.WrapText = True
.MergeCells = True
.Font.Name = "Times New Roman"
.Font.Size = 10
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlEdgeRight).Weight = xlThin
End With
Range("A7").Select
ActiveCell.FormulaR1C1 = "1"
Range("B7").Select
ActiveCell.FormulaR1C1 = "1а"
Range("C7").Select
ActiveCell.FormulaR1C1 = "2"
Range("D7").Select
ActiveCell.FormulaR1C1 = "2а"
Range("E7").Select
ActiveCell.FormulaR1C1 = "3"
Range("F7").Select
ActiveCell.FormulaR1C1 = "4"
Range("G7").Select
ActiveCell.FormulaR1C1 = "5"
Range("H7").Select
ActiveCell.FormulaR1C1 = "6"
Range("I7").Select
ActiveCell.FormulaR1C1 = "7"
Range("J7").Select
ActiveCell.FormulaR1C1 = "8"
Range("K7").Select
ActiveCell.FormulaR1C1 = "9"
Range("L7").Select
ActiveCell.FormulaR1C1 = "10"
Range("M7").Select
ActiveCell.FormulaR1C1 = "10а"
Range("N7").Select
ActiveCell.FormulaR1C1 = "11"
Range("F7,G7,J7,K7").Select
Selection.NumberFormat = "0"
Range("A7:N7").Select
With Selection
.HorizontalAlignment = xlCenter
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlInsideVertical).Weight = xlThin
.Font.Name = "Times New Roman"
.Font.Size = 10
End With
Cells.Find(What:="Итог", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
i = ActiveCell.Row
Cells(i, 1).Select
ActiveCell.FormulaR1C1 = "Всего к оплате за " & mes & " 20" & YearNach & " года"
Range(Cells(i, 1), Cells(i, 11)).Select
Selection.Font.Bold = True
Selection.Borders(xlEdgeBottom).Weight = xlThin
Range(Cells(8, 2), Cells(i, 2)).Select
Selection.HorizontalAlignment = xlCenter
Cells(i, 7).Select
With Selection
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).Weight = xlThin
End With
Cells(i, 10).Select
With Selection
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).Weight = xlThin
End With
Cells(i, 11).Select
With Selection
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).Weight = xlThin
End With
Cells(i, 12).Select
Selection.ClearContents
Cells(8, 12).Select
ActiveCell.FormulaR1C1 = " -"
Cells(8, 13).Select
ActiveCell.FormulaR1C1 = " -"
Cells(8, 14).Select
ActiveCell.FormulaR1C1 = " -"
Range(Cells(8, 12), Cells(i - 1, 14)).Select
Selection.FillDown
'
' подвал
'
Cells(i + 3, 1).Value = "Руководитель организации"
Cells(i + 4, 1).Value = "или иное уполномоченное лицо"
Cells(i + 4, 3).Value = "__________ "
Cells(i + 3, 5).Value = RTrim(DezFio)
Range(Cells(i + 3, 5), Cells(i + 4, 6)).Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = True
.MergeCells = True
.Borders(xlEdgeBottom).Weight = xlThin
End With
Cells(i + 5, 3).Value = "(подпись)"
Cells(i + 5, 5).Value = " (ф.и.о.)"
Cells(i + 3, 7).Value = " Главный бухгалтер"
Cells(i + 4, 7).Value = " или иное уполномоченное лицо"
Cells(i + 4, 10).Value = " ___________ "
Cells(i + 3, 12).Value = RTrim(dezbuchg)
Range(Cells(i + 3, 12), Cells(i + 4, 14)).Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = True
.MergeCells = True
.Borders(xlEdgeBottom).Weight = xlThin
End With
Cells(i + 5, 10).Value = " (подпись)"
Cells(i + 5, 12).Value = " (ф.и.о.)"
Cells(i + 7, 1).Value = "Индивидуальный предприниматель"
Cells(i + 8, 1).Value = "или иное уполномоченное лицо"
Cells(i + 8, 3).Value = "__________ "
' Cells(I + 7, 5).Value = RTrim(DezFio)
Range(Cells(i + 7, 5), Cells(i + 8, 6)).Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = True
.MergeCells = True
.Borders(xlEdgeBottom).Weight = xlThin
End With
Cells(i + 9, 3).Value = "(подпись)"
Cells(i + 9, 5).Value = " (ф.и.о.)"
'''''''''''''''''''''''''''''''?????
'Cells(I + 7, 1).Value = " Индивидуальный"
' Cells(I + 7, 3).Value = "______________ "
' Cells(I + 7, 5).Value = "______________ " ' & RTrim(DezFio)
' Range(Cells(I + 7, 7), Cells(I + 7, 13)).Select
' Selection.Borders(xlEdgeBottom).Weight = xlThin
' 'Cells(I + 7, 5).Select
' 'Selection.Font.Underline = xlUnderlineStyleSingle
' Cells(I + 8, 1).Value = " предприниматель"
' Cells(I + 8, 3).Value = " (подпись)"
' Cells(I + 8, 5).Value = " (ф.и.о.)"
'
Cells(i + 9, 10).Value = "(реквизиты свидетельства о государственной регистрации индивидуального предпринимателя)"
Range(Cells(i + 9, 10), Cells(i + 10, 14)).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.WrapText = True
.MergeCells = True
.Borders(xlEdgeTop).Weight = xlThin
End With
''''''''''''''''''''''''''''''????
Range(Cells(i + 3, 1), Cells(i + 13, 13)).Select
Selection.Font.Name = "Times New Roman"
' Cells(I + 12, 1).Value = "Примечание:1.Первый экземпляр счета-фактуры, составленного на бумажном носителе - покупателю, второй экземпляр-продавцу"
' Cells(I + 13, 1).Value = " 2.При составлении организацией счета-фактуры в электронном виде показатель 'Главный бухгалтер (подпись)(фио)' не формируются"
' Range(Cells(I + 12, 1), Cells(I + 13, 1)).Select
' Selection.Font.Size = 8
'
'
'шапка
'
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Selection.Delete Shift:=xlUp
' Selection.Delete Shift:=xlUp
Range("B1").Select
ActiveCell.FormulaR1C1 = "СЧЕТ - ФАКТУРА N "
With ActiveCell.Characters(Start:=1, Length:=34).Font
.Name = "Times New Roman"
.FontStyle = "полужирный"
.Size = 12
End With
Range("e1").Select
ActiveCell.FormulaR1C1 = num
With Selection
.HorizontalAlignment = xlLeft
'.Font.Underline = xlUnderlineStyleSingle
End With
Range("f1").Select
ActiveCell.FormulaR1C1 = " от "
Selection.Font.Bold = True
Range("G1").Select
ActiveCell.FormulaR1C1 = Day(dat) & "." & monthNach & "." & Year(dat) 'dat & Day(dat)dat
' Selection.Font.Underline = xlUnderlineStyleSingle
Range("E1, G1").Select
With Selection
.HorizontalAlignment = xlLeft
.MergeCells = True
.Borders(xlEdgeBottom).Weight = xlThin
End With
Range("B2").Select
ActiveCell.FormulaR1C1 = "ИСПРАВЛЕНИЕ N -_________________"
Range("F2").Select
ActiveCell.FormulaR1C1 = " от -__________________ "
Selection.Font.Bold = True
' Range("G2").Select
' ActiveCell.FormulaR1C1 = "_________ "
Range("I1:I14").Select
With Selection
.NumberFormat = "@" 'текстовый формат
.HorizontalAlignment = xlLeft
End With
Range("A4").Select
ActiveCell.FormulaR1C1 = " Продавец "
Range("B4").Select
ActiveCell.FormulaR1C1 = " " & RTrim(org)
Range("I4").Select
ActiveCell.FormulaR1C1 = " (2)"
Range("A5").Select
ActiveCell.FormulaR1C1 = " Адрес: "
Range("B5").Select
ActiveCell.FormulaR1C1 = " " & RTrim(dezadr) ' & " тел: " & Deztel
Range("I1").Select
ActiveCell.FormulaR1C1 = " (1)"
Range("I2").Select
ActiveCell.FormulaR1C1 = " (1а)"
Range("I5").Select
ActiveCell.FormulaR1C1 = " (2а)"
Range("A6").Select
ActiveCell.FormulaR1C1 = "ИНН/КПП продавца "
Range("B6").Select
ActiveCell.FormulaR1C1 = " " & dezinn & " / " & Dezkpp
Range("I6").Select
ActiveCell.FormulaR1C1 = " (2б)"
Range("A7").Select
ActiveCell.FormulaR1C1 = "Грузоотправитель и его адрес "
Range("B7").Select
ActiveCell.FormulaR1C1 = " -"
Range("I7").Select
ActiveCell.FormulaR1C1 = " (3)"
Range("A8").Select
ActiveCell.FormulaR1C1 = "Грузополучатель и его адрес "
Range("B8").Select
ActiveCell.FormulaR1C1 = " -"
Range("I8").Select
ActiveCell.FormulaR1C1 = " (4)"
Range("A9").Select
ActiveCell.FormulaR1C1 = "К платежно-расчетному документу "
Range("B9").Select
ActiveCell.FormulaR1C1 = " -"
Range("I9").Select
ActiveCell.FormulaR1C1 = " (5)"
Range("A10").Select
ActiveCell.FormulaR1C1 = "Покупатель "
Range("B10").Select
ActiveCell.FormulaR1C1 = " " & Orgname
Range("A10,b10").Select
Selection.Font.Bold = True
Range("I10").Select
ActiveCell.FormulaR1C1 = " (6)"
Range("A11").Select
ActiveCell.FormulaR1C1 = "Адрес: "
Range("B11").Select
ActiveCell.FormulaR1C1 = " " & OrgAdr 'AdrPom
Range("I11").Select
ActiveCell.FormulaR1C1 = " (6а)"
Range("J11").Select
ActiveCell.FormulaR1C1 = " Код плательщика " & codplat
Range("A12").Select
ActiveCell.FormulaR1C1 = "ИНН/КПП покупателя "
Range("B12").Select
ActiveCell.FormulaR1C1 = " " & orginn & " / " & kpp
Range("I12").Select
ActiveCell.FormulaR1C1 = " (6б)"
Range("J12").Select
ActiveCell.FormulaR1C1 = " Договор N " & RTrim(dogNum) ' & " от" & datDog
Range("A4:J15").Select
With Selection
.Font.Name = "Times New Roman"
.Font.Size = 10
End With
Range("J11:J12").Select
Selection.Font.Bold = True
Range("A13").Select
ActiveCell.FormulaR1C1 = "Валюта: наименование, код "
Range("B13").Select
ActiveCell.FormulaR1C1 = " Российский рубль, 643"
Range("I13").Select
ActiveCell.FormulaR1C1 = " (7)"
Range("A14").Select
ActiveCell.FormulaR1C1 = "Идентификатор государственного контракта, договора(соглашения)(при наличии)"
Range(Cells(14, 6), Cells(14, ).Select
With Selection
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
' .Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).Weight = xlThin
End With
Range("I14").Select
With Selection
.NumberFormat = "@" 'текстовый формат
.HorizontalAlignment = xlLeft
End With
ActiveCell.FormulaR1C1 = " (8)"
Range("B4:H4,B5:H5,B6:H6,B7:H7,B8:H8,B9:H9,B10:H10,B11:H11,B12:H12,B13:H13, A14:H14").Select
With Selection
.HorizontalAlignment = xlLeft
.MergeCells = True
.Borders(xlEdgeBottom).Weight = xlThin
End With
Range("I4, I5,I6,I7,I8,I9,I10,I11,I12,I13,I15").Select
Selection.HorizontalAlignment = xlLeft
Range("A4:A13").Select
Selection.HorizontalAlignment = xlRight
Range("N1").Select
ActiveCell.FormulaR1C1 = "Приложение N 1 "
Range("N2").Select
ActiveCell.FormulaR1C1 = "к постановлению Правительства"
Range("N3").Select
ActiveCell.FormulaR1C1 = "Российской Федерации от 26 декабря 2011 г. N 1137"
Range("N1:N3").Select
With Selection
.Font.Name = "Times New Roman"
.Font.Size = 10
.HorizontalAlignment = xlRight
End With
Range("N4").Select
ActiveCell.FormulaR1C1 = "(в ред. Постановления Правительства РФ от 19.08.2017 N 981)"
With Selection
.Font.Name = "Times New Roman"
.Font.Size = 8
.HorizontalAlignment = xlRight
End With
End Sub |