Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Доработать макрос выгрузки в csv - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин  
Мир MS Excel » Работа и общение » Работа / Фриланс » Доработать макрос выгрузки в csv (Макросы/Sub)
Доработать макрос выгрузки в csv
prmdrk Дата: Понедельник, 19.10.2015, 15:32 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Есть макрос выгрузки в csv, на данный момент выгружает по всем строкам но при этом только по 11 столбцу (40 неделя) нужно чтобы выгружал и по всем остальным неделям. Проблема в цикле, там задана переменной строка, а столбец задать строго 11.
[vba]
Код
Public Mwb As Workbook
Public Mwb2 As Workbook
Public Asheet As Worksheet
Public Bsheet As Worksheet
Public TransWbMaxRow As Variant
Public TransWbMaxCol As Variant
Public TransWbMaxRow2 As Variant
Public TransWbMaxCol2 As Variant

Public Prod_Code(10000) As Variant
Public Prod_Code1(10000) As Variant
Public Prod_Code2(10000) As Variant
Public Prod_Code3(10000) As Variant
Public Prod_Quantity(100000) As Variant
Public Prod_Date(100000) As Variant
Public FU(100000) As Variant
Public PV(100000) As Variant
Public NFA(100000) As Variant
Public RFA(100000) As Variant
Public Prod_year(100000) As Variant
Public months(100000) As Variant
Public weeks(100000) As Variant

Public Prod_Name_Missed(500) As Variant
Public Prod_Code_Missed(500) As Variant
Function GetMaxRow() As Variant
    On Error GoTo met2
    GetMaxRow = ActiveCell.SpecialCells(xlLastCell).Row
    Exit Function
met2:
    Resume Next
End Function
Function GetMaxCol() As Integer
    On Error GoTo met1
    GetMaxCol = ActiveCell.SpecialCells(xlLastCell).Column
    Exit Function
met1:
    Resume Next
End Function
Function fnd()
On Error GoTo err_debug
Exit Function
err_debug:
MsgBox Err.Number & ": " & Err.Description & " on line " & Erl, vbError
End Function

Sub unload_csv()
Set Mwb = ActiveWorkbook
Set Asheet = Mwb.ActiveSheet



countmess% = 0
Date1 = ""
aaa% = MsgBox("Áóäåò ñîçäàí csv ôàéë äëÿ çàãðóçêè â SAP.", vbOKCancel + vbInformation, "Âíèìàíèå.")
If aaa% <> vbOK Then
    End
End If
Application.ScreenUpdating = False
TransWbMaxRow = GetMaxRow()


cnt1# = 0
For Ii# = 8 To TransWbMaxRow

    If Len(Trim(Cells(Ii#, 1).Value)) > 0 Then
        cnt1# = cnt1# + 1
        Prod_Code(cnt1#) = Trim(Cells(Ii#, 9).Value)
        Prod_Code1(cnt1#) = Trim(Cells(Ii#, 6).Value)
        Prod_Code2(cnt1#) = Trim(Cells(Ii#, 7).Value)
        Prod_Code3(cnt1#) = Trim(Cells(Ii#, 8).Value)
        Prod_Quantity(cnt1#) = Trim(Cells(Ii#, 11).Value)
        FU(cnt1#) = Trim(Cells(Ii#, 1).Value)
        PV(cnt1#) = Trim(Cells(Ii#, 2).Value)
        NFA(cnt1#) = Trim(Cells(Ii#, 3).Value)
        RFA(cnt1#) = Trim(Cells(Ii#, 4).Value)

    End If
Next Ii#
         
     
     
    Application.ScreenUpdating = False
TransWbMaxCol = GetMaxCol()
    cnt2# = 0
For jj# = 11 To TransWbMaxCol
    If Len(Trim(Cells(jj#, 1).Value)) > 0 Then
        cnt2# = cnt2# + 1
        months(cnt2#) = Trim(Cells(7, jj#).Value)
        weeks(cnt2#) = Trim(Cells(1, jj#).Value)
    End If
Next jj#
         
         
         
    Jk = FreeFile()
    Open "C:\APO\0001.csv" For Output As Jk
    Print #Jk, "APO - Plng Version" & ";" & "Sales Org" & ";" & "Product" & ";" & "Forecast Unit" & ";" & "Product Variant" & ";"; "National Forecast Ac"& ";" & "Regional Forecast Ac" & ";" & "Customer country" & ";" & "APO - Location" & ";" & "Distribution Channel" & ";" & "Calendar week" & ";" &"Calendar Months" & ";" & "Unit of measure" & ";" & "Budget" & ";" & "Reestimate" & ";" & "Historical Mark events" & ";" & "Historical Other events" &";" & "Historical sales events" & ";" & "Base forecast"; ";" & "Base forecast correction" & ";" & "Base forecast distribution" & ";" & "Marketing events"& ";" & "Sales events" & ";" & "Other events" & ";" & "Future events4" & ";" & "Additional events" & ";" & "Artkey" & ";" & "Timedis" & ";" & "PWF"
    Ln# = 2
    For jj1# = 1 To cnt2#
    For Ii1# = 1 To cnt1#
    If NFA(Ii1#) = "089030" Then
    If Prod_Code1(Ii1#) = 0 Then
    Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" &"5201" & ";" & "06" & ";" & weeks(jj1#) & ";" & months(jj1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "0" & ";" &"0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & Round(Prod_Quantity(Ii1#), 0) & ";" & "0" & ";" & "0" & ";" & "0"
    Else
    Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code1(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" &"5201" & ";" & "06" & ";" & weeks(jj1#) & ";" & months(jj1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "0" & ";" &"0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & Round(Prod_Quantity(Ii1#), 0) & ";" & "0" & ";" & "0" & ";" & "0"
    Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" &"5201" & ";" & "06" & ";" & weeks(jj1#) & ";" & months(jj1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "0" & ";" &"0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "1" & ";" & "0" & ";" & "0" & ";" & "0"
    End If
    Else

    If Prod_Code1(Ii1#) = 0 And Prod_Code2(Ii1#) <> "empty" And Prod_Code3(Ii1#) <> "empty" Then
        Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" &"5201" & ";" & "06" & ";" & weeks(jj1#) & ";" & months(jj1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" &Round(Prod_Quantity(Ii1#), 0) & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"
        If Prod_Code2(Ii1#) > 0 And Prod_Code2(Ii1#) <> "empty" Then
        Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code2(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" &"5201" & ";" & "06" & ";" & weeks(jj1#) & ";" & months(jj1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "1" & ";" &"0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"
        End If
        If Prod_Code3(Ii1#) > 0 And Prod_Code3(Ii1#) <> "empty" Then
        Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code3(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" &"5201" & ";" & "06" & ";" & weeks(jj1#) & ";" & months(jj1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "1" & ";" &"0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"
        End If
    Else
        Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code1(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" &"5201" & ";" & "06" & ";" & weeks(jj1#) & ";" & months(jj1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" &Round(Prod_Quantity(Ii1#), 0) & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"
        Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" &"5201" & ";" & "06" & ";" & weeks(jj1#) & ";" & months(jj1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "1" & ";" &"0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"
        If Prod_Code2(Ii1#) > 0 And Prod_Code2(Ii1#) <> "empty" Then
        Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code2(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" &"5201" & ";" & "06" & ";" & weeks(jj1#) & ";" & months(jj1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "1" & ";" &"0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"
        End If
        If Prod_Code3(Ii1#) > 0 And Prod_Code3(Ii1#) <> "empty" Then
        Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code3(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" &"5201" & ";" & "06" & ";" & weeks(jj1#) & ";" & months(jj1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "1" & ";" &"0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"
        End If
    End If
    End If
    Next Ii1#
    Next jj1#
     
        ' Print #jj%, Val(Cells(i%, 3).Value) & ",", Mid(Sums$, 1, Len(Sums$) - 1)
    Close #Jk

    Application.ScreenUpdating = True
End Sub
[/vba]
К сообщению приложен файл: 0381176.xls (62.0 Kb)


Сообщение отредактировал prmdrk - Понедельник, 19.10.2015, 15:32
 
Ответить
СообщениеЕсть макрос выгрузки в csv, на данный момент выгружает по всем строкам но при этом только по 11 столбцу (40 неделя) нужно чтобы выгружал и по всем остальным неделям. Проблема в цикле, там задана переменной строка, а столбец задать строго 11.
[vba]
Код
Public Mwb As Workbook
Public Mwb2 As Workbook
Public Asheet As Worksheet
Public Bsheet As Worksheet
Public TransWbMaxRow As Variant
Public TransWbMaxCol As Variant
Public TransWbMaxRow2 As Variant
Public TransWbMaxCol2 As Variant

Public Prod_Code(10000) As Variant
Public Prod_Code1(10000) As Variant
Public Prod_Code2(10000) As Variant
Public Prod_Code3(10000) As Variant
Public Prod_Quantity(100000) As Variant
Public Prod_Date(100000) As Variant
Public FU(100000) As Variant
Public PV(100000) As Variant
Public NFA(100000) As Variant
Public RFA(100000) As Variant
Public Prod_year(100000) As Variant
Public months(100000) As Variant
Public weeks(100000) As Variant

Public Prod_Name_Missed(500) As Variant
Public Prod_Code_Missed(500) As Variant
Function GetMaxRow() As Variant
    On Error GoTo met2
    GetMaxRow = ActiveCell.SpecialCells(xlLastCell).Row
    Exit Function
met2:
    Resume Next
End Function
Function GetMaxCol() As Integer
    On Error GoTo met1
    GetMaxCol = ActiveCell.SpecialCells(xlLastCell).Column
    Exit Function
met1:
    Resume Next
End Function
Function fnd()
On Error GoTo err_debug
Exit Function
err_debug:
MsgBox Err.Number & ": " & Err.Description & " on line " & Erl, vbError
End Function

Sub unload_csv()
Set Mwb = ActiveWorkbook
Set Asheet = Mwb.ActiveSheet



countmess% = 0
Date1 = ""
aaa% = MsgBox("Áóäåò ñîçäàí csv ôàéë äëÿ çàãðóçêè â SAP.", vbOKCancel + vbInformation, "Âíèìàíèå.")
If aaa% <> vbOK Then
    End
End If
Application.ScreenUpdating = False
TransWbMaxRow = GetMaxRow()


cnt1# = 0
For Ii# = 8 To TransWbMaxRow

    If Len(Trim(Cells(Ii#, 1).Value)) > 0 Then
        cnt1# = cnt1# + 1
        Prod_Code(cnt1#) = Trim(Cells(Ii#, 9).Value)
        Prod_Code1(cnt1#) = Trim(Cells(Ii#, 6).Value)
        Prod_Code2(cnt1#) = Trim(Cells(Ii#, 7).Value)
        Prod_Code3(cnt1#) = Trim(Cells(Ii#, 8).Value)
        Prod_Quantity(cnt1#) = Trim(Cells(Ii#, 11).Value)
        FU(cnt1#) = Trim(Cells(Ii#, 1).Value)
        PV(cnt1#) = Trim(Cells(Ii#, 2).Value)
        NFA(cnt1#) = Trim(Cells(Ii#, 3).Value)
        RFA(cnt1#) = Trim(Cells(Ii#, 4).Value)

    End If
Next Ii#
         
     
     
    Application.ScreenUpdating = False
TransWbMaxCol = GetMaxCol()
    cnt2# = 0
For jj# = 11 To TransWbMaxCol
    If Len(Trim(Cells(jj#, 1).Value)) > 0 Then
        cnt2# = cnt2# + 1
        months(cnt2#) = Trim(Cells(7, jj#).Value)
        weeks(cnt2#) = Trim(Cells(1, jj#).Value)
    End If
Next jj#
         
         
         
    Jk = FreeFile()
    Open "C:\APO\0001.csv" For Output As Jk
    Print #Jk, "APO - Plng Version" & ";" & "Sales Org" & ";" & "Product" & ";" & "Forecast Unit" & ";" & "Product Variant" & ";"; "National Forecast Ac"& ";" & "Regional Forecast Ac" & ";" & "Customer country" & ";" & "APO - Location" & ";" & "Distribution Channel" & ";" & "Calendar week" & ";" &"Calendar Months" & ";" & "Unit of measure" & ";" & "Budget" & ";" & "Reestimate" & ";" & "Historical Mark events" & ";" & "Historical Other events" &";" & "Historical sales events" & ";" & "Base forecast"; ";" & "Base forecast correction" & ";" & "Base forecast distribution" & ";" & "Marketing events"& ";" & "Sales events" & ";" & "Other events" & ";" & "Future events4" & ";" & "Additional events" & ";" & "Artkey" & ";" & "Timedis" & ";" & "PWF"
    Ln# = 2
    For jj1# = 1 To cnt2#
    For Ii1# = 1 To cnt1#
    If NFA(Ii1#) = "089030" Then
    If Prod_Code1(Ii1#) = 0 Then
    Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" &"5201" & ";" & "06" & ";" & weeks(jj1#) & ";" & months(jj1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "0" & ";" &"0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & Round(Prod_Quantity(Ii1#), 0) & ";" & "0" & ";" & "0" & ";" & "0"
    Else
    Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code1(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" &"5201" & ";" & "06" & ";" & weeks(jj1#) & ";" & months(jj1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "0" & ";" &"0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & Round(Prod_Quantity(Ii1#), 0) & ";" & "0" & ";" & "0" & ";" & "0"
    Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" &"5201" & ";" & "06" & ";" & weeks(jj1#) & ";" & months(jj1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "0" & ";" &"0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "1" & ";" & "0" & ";" & "0" & ";" & "0"
    End If
    Else

    If Prod_Code1(Ii1#) = 0 And Prod_Code2(Ii1#) <> "empty" And Prod_Code3(Ii1#) <> "empty" Then
        Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" &"5201" & ";" & "06" & ";" & weeks(jj1#) & ";" & months(jj1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" &Round(Prod_Quantity(Ii1#), 0) & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"
        If Prod_Code2(Ii1#) > 0 And Prod_Code2(Ii1#) <> "empty" Then
        Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code2(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" &"5201" & ";" & "06" & ";" & weeks(jj1#) & ";" & months(jj1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "1" & ";" &"0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"
        End If
        If Prod_Code3(Ii1#) > 0 And Prod_Code3(Ii1#) <> "empty" Then
        Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code3(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" &"5201" & ";" & "06" & ";" & weeks(jj1#) & ";" & months(jj1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "1" & ";" &"0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"
        End If
    Else
        Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code1(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" &"5201" & ";" & "06" & ";" & weeks(jj1#) & ";" & months(jj1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" &Round(Prod_Quantity(Ii1#), 0) & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"
        Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" &"5201" & ";" & "06" & ";" & weeks(jj1#) & ";" & months(jj1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "1" & ";" &"0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"
        If Prod_Code2(Ii1#) > 0 And Prod_Code2(Ii1#) <> "empty" Then
        Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code2(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" &"5201" & ";" & "06" & ";" & weeks(jj1#) & ";" & months(jj1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "1" & ";" &"0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"
        End If
        If Prod_Code3(Ii1#) > 0 And Prod_Code3(Ii1#) <> "empty" Then
        Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code3(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" &"5201" & ";" & "06" & ";" & weeks(jj1#) & ";" & months(jj1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "1" & ";" &"0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"
        End If
    End If
    End If
    Next Ii1#
    Next jj1#
     
        ' Print #jj%, Val(Cells(i%, 3).Value) & ",", Mid(Sums$, 1, Len(Sums$) - 1)
    Close #Jk

    Application.ScreenUpdating = True
End Sub
[/vba]
Автор - prmdrk
Дата добавления - 19.10.2015 в 15:32
prmdrk Дата: Понедельник, 19.10.2015, 16:41 | Сообщение № 2
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Попробовал задать колонку как переменную и засунуть в цикл, теперь выдает ошибку Run-time error '13' Type mismatch после того как выгружает первую строку по всем столбцам, дебагер выделяется 97 строку.
[vba]
Код
Public Mwb As Workbook
Public Mwb2 As Workbook
Public Asheet As Worksheet
Public Bsheet As Worksheet
Public TransWbMaxRow As Variant
Public TransWbMaxCol As Variant
Public TransWbMaxRow2 As Variant
Public TransWbMaxCol2 As Variant

Public Prod_Code(1000000) As Variant
Public Prod_Code1(1000000) As Variant
Public Prod_Code2(1000000) As Variant
Public Prod_Code3(1000000) As Variant
Public Prod_Quantity(1000000) As Variant
Public Prod_Date(1000000) As Variant
Public FU(1000000) As Variant
Public PV(1000000) As Variant
Public NFA(1000000) As Variant
Public RFA(1000000) As Variant
Public Prod_year(1000000) As Variant
Public months(1000000) As Variant
Public weeks(1000000) As Variant


Public Prod_Name_Missed(500) As Variant
Public Prod_Code_Missed(500) As Variant
Function GetMaxRow() As Long
    On Error GoTo met2
    GetMaxRow = ActiveCell.SpecialCells(xlLastCell).Row
    Exit Function
met2:
    Resume Next
End Function
Function GetMaxCol() As Long
    On Error GoTo met1
    GetMaxCol = ActiveCell.SpecialCells(xlLastCell).Column
    Exit Function
met1:
    Resume Next
End Function
Function fnd()
On Error GoTo err_debug
Exit Function
err_debug:
MsgBox Err.Number & ": " & Err.Description & " on line " & Erl, vbError
End Function

Sub unload_csv()
   Set Mwb = ActiveWorkbook
   Set Asheet = Mwb.ActiveSheet
    
    
    
   countmess% = 0
   Date1 = ""
   aaa% = MsgBox("Áóäåò ñîçäàí csv ôàéë äëÿ àâòîìàòè÷åñêîé çàãðóçêè â SAP.", vbOKCancel + vbInformation, "Âíèìàíèå.")
   If aaa% <> vbOK Then
    End
   End If
    
   w$ = Range("I3").Value
     
   Application.ScreenUpdating = False
   TransWbMaxCol = GetMaxCol()
   TransWbMaxRow = GetMaxRow()
cnt1# = 0
For Ii# = 8 To TransWbMaxRow
For jj# = w$ To TransWbMaxCol
If Len(Trim(Cells(Ii#, 1).Value)) > 0 And Len(Trim(Cells(jj#, 1).Value)) > 0 Then
        cnt1# = cnt1# + 1
        Prod_Code(cnt1#) = Trim(Cells(Ii#, 9).Value)
        Prod_Code1(cnt1#) = Trim(Cells(Ii#, 6).Value)
        Prod_Code2(cnt1#) = Trim(Cells(Ii#, 7).Value)
        Prod_Code3(cnt1#) = Trim(Cells(Ii#, 8).Value)
        FU(cnt1#) = Trim(Cells(Ii#, 1).Value)
        PV(cnt1#) = Trim(Cells(Ii#, 2).Value)
        NFA(cnt1#) = Trim(Cells(Ii#, 3).Value)
        RFA(cnt1#) = Trim(Cells(Ii#, 4).Value)
   Prod_Quantity(cnt1#) = Trim(Cells(Ii#, jj#).Value)
    months(cnt1#) = Trim(Cells(7, jj#).Value)
    weeks(cnt1#) = Trim(Cells(1, jj#).Value)
     End If
Next jj#
Next Ii#

      

         
    Jk = FreeFile()
    Open "C:\APO\0001.csv" For Output As Jk
    Print #Jk, "APO - Plng Version" & ";" & "Sales Org" & ";" & "Product" & ";" & "Forecast Unit" & ";" & "Product Variant" & ";"; "National Forecast Ac" & ";" & "Regional Forecast Ac" & ";" & "Customer country" & ";" & "APO - Location" & ";" & "Distribution Channel" & ";" & "Calendar week" & ";" & "Calendar Months" & ";" & "Unit of measure" & ";" & "Budget" & ";" & "Reestimate" & ";" & "Historical Mark events" & ";" & "Historical Other events" & ";" & "Historical sales events" & ";" & "Base forecast"; ";" & "Base forecast correction" & ";" & "Base forecast distribution" & ";" & "Marketing events" & ";" & "Sales events" & ";" & "Other events" & ";" & "Future events4" & ";" & "Additional events" & ";" & "Artkey" & ";" & "Timedis" & ";" & "PWF"
    Ln# = 2
   For Ii1# = 1 To cnt1#
     
          If NFA(Ii1#) = "089030" Then
       If Prod_Code1(Ii1#) = 0 Then
       Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" & "5201" & ";" & "06" & ";" & weeks(Ii1#) & ";" & months(Ii1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & Round(Prod_Quantity(Ii1#), 0) & ";" & "0" & ";" & "0" & ";" & "0"
       Else
       Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code1(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" & "5201" & ";" & "06" & ";" & weeks(Ii1#) & ";" & months(Ii1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & Round(Prod_Quantity(Ii1#), 0) & ";" & "0" & ";" & "0" & ";" & "0"
       Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" & "5201" & ";" & "06" & ";" & weeks(Ii1#) & ";" & months(Ii1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "1" & ";" & "0" & ";" & "0" & ";" & "0"
       End If
    Else
          If Prod_Code1(Ii1#) = 0 And Prod_Code2(Ii1#) <> "empty" And Prod_Code3(Ii1#) <> "empty" Then
        Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" & "5201" & ";" & "06" & ";" & weeks(Ii1#) & ";" & months(Ii1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & Round(Prod_Quantity(Ii1#), 0) & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"
        If Prod_Code2(Ii1#) > 0 And Prod_Code2(Ii1#) <> "empty" Then
        Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code2(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" & "5201" & ";" & "06" & ";" & weeks(Ii1#) & ";" & months(Ii1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "1" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"
        End If
        If Prod_Code3(Ii1#) > 0 And Prod_Code3(Ii1#) <> "empty" Then
        Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code3(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" & "5201" & ";" & "06" & ";" & weeks(Ii1#) & ";" & months(Ii1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "1" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"
        End If
       Else
        Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code1(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" & "5201" & ";" & "06" & ";" & weeks(Ii1#) & ";" & months(Ii1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & Round(Prod_Quantity(Ii1#), 0) & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"
        Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" & "5201" & ";" & "06" & ";" & weeks(Ii1#) & ";" & months(Ii1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "1" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"
        If Prod_Code2(Ii1#) > 0 And Prod_Code2(Ii1#) <> "empty" Then
        Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code2(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" & "5201" & ";" & "06" & ";" & weeks(Ii1#) & ";" & months(Ii1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "1" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"
        End If
        If Prod_Code3(Ii1#) > 0 And Prod_Code3(Ii1#) <> "empty" Then
        Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code3(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" & "5201" & ";" & "06" & ";" & weeks(Ii1#) & ";" & months(Ii1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "1" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"
        End If
       End If
    End If

     Next Ii1#

     
           ' Print #jj%, Val(Cells(i%, 3).Value) & ",", Mid(Sums$, 1, Len(Sums$) - 1)
    Close #Jk

    Application.ScreenUpdating = True
End Sub
[/vba]


Сообщение отредактировал prmdrk - Понедельник, 19.10.2015, 16:42
 
Ответить
СообщениеПопробовал задать колонку как переменную и засунуть в цикл, теперь выдает ошибку Run-time error '13' Type mismatch после того как выгружает первую строку по всем столбцам, дебагер выделяется 97 строку.
[vba]
Код
Public Mwb As Workbook
Public Mwb2 As Workbook
Public Asheet As Worksheet
Public Bsheet As Worksheet
Public TransWbMaxRow As Variant
Public TransWbMaxCol As Variant
Public TransWbMaxRow2 As Variant
Public TransWbMaxCol2 As Variant

Public Prod_Code(1000000) As Variant
Public Prod_Code1(1000000) As Variant
Public Prod_Code2(1000000) As Variant
Public Prod_Code3(1000000) As Variant
Public Prod_Quantity(1000000) As Variant
Public Prod_Date(1000000) As Variant
Public FU(1000000) As Variant
Public PV(1000000) As Variant
Public NFA(1000000) As Variant
Public RFA(1000000) As Variant
Public Prod_year(1000000) As Variant
Public months(1000000) As Variant
Public weeks(1000000) As Variant


Public Prod_Name_Missed(500) As Variant
Public Prod_Code_Missed(500) As Variant
Function GetMaxRow() As Long
    On Error GoTo met2
    GetMaxRow = ActiveCell.SpecialCells(xlLastCell).Row
    Exit Function
met2:
    Resume Next
End Function
Function GetMaxCol() As Long
    On Error GoTo met1
    GetMaxCol = ActiveCell.SpecialCells(xlLastCell).Column
    Exit Function
met1:
    Resume Next
End Function
Function fnd()
On Error GoTo err_debug
Exit Function
err_debug:
MsgBox Err.Number & ": " & Err.Description & " on line " & Erl, vbError
End Function

Sub unload_csv()
   Set Mwb = ActiveWorkbook
   Set Asheet = Mwb.ActiveSheet
    
    
    
   countmess% = 0
   Date1 = ""
   aaa% = MsgBox("Áóäåò ñîçäàí csv ôàéë äëÿ àâòîìàòè÷åñêîé çàãðóçêè â SAP.", vbOKCancel + vbInformation, "Âíèìàíèå.")
   If aaa% <> vbOK Then
    End
   End If
    
   w$ = Range("I3").Value
     
   Application.ScreenUpdating = False
   TransWbMaxCol = GetMaxCol()
   TransWbMaxRow = GetMaxRow()
cnt1# = 0
For Ii# = 8 To TransWbMaxRow
For jj# = w$ To TransWbMaxCol
If Len(Trim(Cells(Ii#, 1).Value)) > 0 And Len(Trim(Cells(jj#, 1).Value)) > 0 Then
        cnt1# = cnt1# + 1
        Prod_Code(cnt1#) = Trim(Cells(Ii#, 9).Value)
        Prod_Code1(cnt1#) = Trim(Cells(Ii#, 6).Value)
        Prod_Code2(cnt1#) = Trim(Cells(Ii#, 7).Value)
        Prod_Code3(cnt1#) = Trim(Cells(Ii#, 8).Value)
        FU(cnt1#) = Trim(Cells(Ii#, 1).Value)
        PV(cnt1#) = Trim(Cells(Ii#, 2).Value)
        NFA(cnt1#) = Trim(Cells(Ii#, 3).Value)
        RFA(cnt1#) = Trim(Cells(Ii#, 4).Value)
   Prod_Quantity(cnt1#) = Trim(Cells(Ii#, jj#).Value)
    months(cnt1#) = Trim(Cells(7, jj#).Value)
    weeks(cnt1#) = Trim(Cells(1, jj#).Value)
     End If
Next jj#
Next Ii#

      

         
    Jk = FreeFile()
    Open "C:\APO\0001.csv" For Output As Jk
    Print #Jk, "APO - Plng Version" & ";" & "Sales Org" & ";" & "Product" & ";" & "Forecast Unit" & ";" & "Product Variant" & ";"; "National Forecast Ac" & ";" & "Regional Forecast Ac" & ";" & "Customer country" & ";" & "APO - Location" & ";" & "Distribution Channel" & ";" & "Calendar week" & ";" & "Calendar Months" & ";" & "Unit of measure" & ";" & "Budget" & ";" & "Reestimate" & ";" & "Historical Mark events" & ";" & "Historical Other events" & ";" & "Historical sales events" & ";" & "Base forecast"; ";" & "Base forecast correction" & ";" & "Base forecast distribution" & ";" & "Marketing events" & ";" & "Sales events" & ";" & "Other events" & ";" & "Future events4" & ";" & "Additional events" & ";" & "Artkey" & ";" & "Timedis" & ";" & "PWF"
    Ln# = 2
   For Ii1# = 1 To cnt1#
     
          If NFA(Ii1#) = "089030" Then
       If Prod_Code1(Ii1#) = 0 Then
       Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" & "5201" & ";" & "06" & ";" & weeks(Ii1#) & ";" & months(Ii1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & Round(Prod_Quantity(Ii1#), 0) & ";" & "0" & ";" & "0" & ";" & "0"
       Else
       Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code1(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" & "5201" & ";" & "06" & ";" & weeks(Ii1#) & ";" & months(Ii1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & Round(Prod_Quantity(Ii1#), 0) & ";" & "0" & ";" & "0" & ";" & "0"
       Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" & "5201" & ";" & "06" & ";" & weeks(Ii1#) & ";" & months(Ii1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "1" & ";" & "0" & ";" & "0" & ";" & "0"
       End If
    Else
          If Prod_Code1(Ii1#) = 0 And Prod_Code2(Ii1#) <> "empty" And Prod_Code3(Ii1#) <> "empty" Then
        Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" & "5201" & ";" & "06" & ";" & weeks(Ii1#) & ";" & months(Ii1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & Round(Prod_Quantity(Ii1#), 0) & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"
        If Prod_Code2(Ii1#) > 0 And Prod_Code2(Ii1#) <> "empty" Then
        Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code2(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" & "5201" & ";" & "06" & ";" & weeks(Ii1#) & ";" & months(Ii1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "1" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"
        End If
        If Prod_Code3(Ii1#) > 0 And Prod_Code3(Ii1#) <> "empty" Then
        Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code3(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" & "5201" & ";" & "06" & ";" & weeks(Ii1#) & ";" & months(Ii1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "1" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"
        End If
       Else
        Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code1(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" & "5201" & ";" & "06" & ";" & weeks(Ii1#) & ";" & months(Ii1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & Round(Prod_Quantity(Ii1#), 0) & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"
        Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" & "5201" & ";" & "06" & ";" & weeks(Ii1#) & ";" & months(Ii1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "1" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"
        If Prod_Code2(Ii1#) > 0 And Prod_Code2(Ii1#) <> "empty" Then
        Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code2(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" & "5201" & ";" & "06" & ";" & weeks(Ii1#) & ";" & months(Ii1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "1" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"
        End If
        If Prod_Code3(Ii1#) > 0 And Prod_Code3(Ii1#) <> "empty" Then
        Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code3(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" & "5201" & ";" & "06" & ";" & weeks(Ii1#) & ";" & months(Ii1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "1" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"
        End If
       End If
    End If

     Next Ii1#

     
           ' Print #jj%, Val(Cells(i%, 3).Value) & ",", Mid(Sums$, 1, Len(Sums$) - 1)
    Close #Jk

    Application.ScreenUpdating = True
End Sub
[/vba]
Автор - prmdrk
Дата добавления - 19.10.2015 в 16:41
prmdrk Дата: Понедельник, 19.10.2015, 18:04 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Спасибо. Тему можно закрыть. Разобрался сам. Проблема была в функции округления во время выгрузки, добавил округление в цикл и все заработало.
 
Ответить
СообщениеСпасибо. Тему можно закрыть. Разобрался сам. Проблема была в функции округления во время выгрузки, добавил округление в цикл и все заработало.
Автор - prmdrk
Дата добавления - 19.10.2015 в 18:04
Мир MS Excel » Работа и общение » Работа / Фриланс » Доработать макрос выгрузки в csv (Макросы/Sub)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!