Syntax error near ','.

Y

yannine

New Member
Joined
Jan 8, 2018
Messages
7
Hi, I have been struggling to sort out the Syntax error ','. in the code below. I had to increase the size of the SQL database to include extra columns for bottle information and since having done so, I keep getting an error. I have spent hours going through my lines of code and cannot find the error. I will be extremely grateful for your help. Thank you! :)

VBA Code: 
Private Sub CommandButton1_Click()

Cells.Select
    Selection.Replace What:="FALSE", Replacement:="-", Lookat:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
  Dim conn As New ADODB.Connection
    Dim iRowNo As Integer
 

Dim sPreprod, sDescription, sClient, sInitiated, sF_Code, sRevised, sCategory, sSales_rep, sUnit_order, sQty_required, sS_O, sUnit_Price, sAmortisation, sSubstrate_colour, sLength, sMaterial, sOrifice, sFoiling, sFoil_Wad, sTube_Ø, sPrint_colours, sShoulder_col, sCap_colour, sCap_Ø, sCap_style, sCap_material, sCap_foil, sFuji, sOther_info, sArtwork, sAW_order, sAW_received, sMachine, sVarnish, sFiller, sStatus, sMould_no, sDrawing_no, sExtrusion_Trial, sFoil_Trial, sInjection_Trial, sBlowmoulding_Trial, sHinterkopf_Trial, sPad_Printing_Trial, sDubuit_Trial, sMoss_SS_Trial, sOmso_SS_Trial, sOmso_OffSet_Trial, sK9_SS_Trial, sKamman_SS_Trial, sDigital_Trial, sR_Code, sWorksOrder, sDue_SAESA, sT1_E_D, sT2_E_D, sT3_E_D, sT4_E_D, sT5_E_D, sPigment_MBatch_supplier_E_D, sPigment_MBatch_grade_E_D, sPigment_MBatch_percentage_E_D, sT1_F_I_B, sT2_F_I_B, sT3_F_I_B, sT4_F_I_B, T5_F_I_B, sPigment_MBatch_supplier_F_I_B, sPigment_MBatch_grade_F_I_B, sPigment_MBatch_percentage_F_I_B, _
sClosedDate, sDrawings, sGrownSampleRequiredsArticleWeight_PreformWeight, sSpecificCapacity, sBrimfull, sFillHeight, sHeight, sWidth, sDepth, sCapToFitBottle, sPET_PreformNeck, sMachineToBeUsed, sCentres, sCavities, sDesignBrief As String

    With Sheets("PP_Info")
            
        'Open a connection to SQL Server
        conn.Open "Provider=SQLOLEDB; Data Source=BP19DT015\SQLEXPRESS01;Initial Catalog=TrackerA;Integrated Security=SSPI;"
            
        'Skip the header row
        iRowNo = 2
            
        'Loop until empty cell in Preprod
        Do Until .Cells(iRowNo, 1) = ""
         sPreprod = .Cells(iRowNo, 1)
            sDescription = .Cells(iRowNo, 2)
            sClient = .Cells(iRowNo, 3)
            sInitiated = .Cells(iRowNo, 4)
            sF_Code = .Cells(iRowNo, 5)
            sRevised = .Cells(iRowNo, 6)
            sCategory = .Cells(iRowNo, 7)
            sSales_rep = .Cells(iRowNo, 8)
            sUnit_order = .Cells(iRowNo, 9)
            sQty_required = .Cells(iRowNo, 10)
            sS_O = .Cells(iRowNo, 11)
            sUnit_Price = .Cells(iRowNo, 12)
            sAmortisation = .Cells(iRowNo, 13)
            sSubstrate_colour = .Cells(iRowNo, 14)
            sLength = .Cells(iRowNo, 15)
            sMaterial = .Cells(iRowNo, 16)
            sOrifice = .Cells(iRowNo, 17)
            sFoiling = .Cells(iRowNo, 18)
            sFoil_Wad = .Cells(iRowNo, 19)
            sTube_Ø = .Cells(iRowNo, 20)
            sPrint_colours = .Cells(iRowNo, 21)
            sShoulder_col = .Cells(iRowNo, 22)
            sCap_colour = .Cells(iRowNo, 23)
            sCap_Ø = .Cells(iRowNo, 24)
            sCap_style = .Cells(iRowNo, 25)
            sCap_material = .Cells(iRowNo, 26)
            sCap_foil = .Cells(iRowNo, 27)
            sFuji = .Cells(iRowNo, 28)
            sOther_info = .Cells(iRowNo, 29)
            sArtwork = .Cells(iRowNo, 30)
            sAW_order = .Cells(iRowNo, 31)
            sAW_received = .Cells(iRowNo, 32)
            sMachine = .Cells(iRowNo, 33)
            sVarnish = .Cells(iRowNo, 34)
            sFiller = .Cells(iRowNo, 35)
            sStatus = .Cells(iRowNo, 36)
            sMould_no = .Cells(iRowNo, 37)
            sDrawing_no = .Cells(iRowNo, 38)
            sExtrusion_Trial = .Cells(iRowNo, 39)
            sFoil_Trial = .Cells(iRowNo, 40)
            sInjection_Trial = .Cells(iRowNo, 41)
            sBlowmoulding_Trial = .Cells(iRowNo, 42)
            sHinterkopf_Trial = .Cells(iRowNo, 43)
            sPad_Printing_Trial = .Cells(iRowNo, 44)
            sDubuit_Trial = .Cells(iRowNo, 45)
            sMoss_SS_Trial = .Cells(iRowNo, 46)
            sOmso_SS_Trial = .Cells(iRowNo, 47)
            sOmso_OffSet_Trial = .Cells(iRowNo, 48)
            sK9_SS_Trial = .Cells(iRowNo, 49)
            sKamman_SS_Trial = .Cells(iRowNo, 50)
            sDigital_Trial = .Cells(iRowNo, 51)
            sR_Code = .Cells(iRowNo, 52)
            sWorksOrder = .Cells(iRowNo, 53)
            sDue_SAESA = .Cells(iRowNo, 54)
            sT1_E_D = .Cells(iRowNo, 55)
            sT2_E_D = .Cells(iRowNo, 56)
            sT3_E_D = .Cells(iRowNo, 57)
            sT4_E_D = .Cells(iRowNo, 58)
            sT5_E_D = .Cells(iRowNo, 59)
            sPigment_MBatch_supplier_E_D = .Cells(iRowNo, 60)
            sPigment_MBatch_grade_E_D = .Cells(iRowNo, 61)
            sPigment_MBatch_percentage_E_D = .Cells(iRowNo, 62)
            sT1_F_I_B = .Cells(iRowNo, 63)
            sT2_F_I_B = .Cells(iRowNo, 64)
            sT3_F_I_B = .Cells(iRowNo, 65)
            sT4_F_I_B = .Cells(iRowNo, 66)
            sT5_F_I_B = .Cells(iRowNo, 67)
            sPigment_MBatch_supplier_F_I_B = .Cells(iRowNo, 68)
            sPigment_MBatch_grade_F_I_B = .Cells(iRowNo, 69)
            sPigment_MBatch_percentage_F_I_B = .Cells(iRowNo, 70)
            sClosedDate = .Cells(iRowNo, 71)
            sDrawings = .Cells(iRowNo, 72)
            sGrownSampleRequired = .Cells(iRowNo, 73)
            sArticleWeight_PreformWeight = .Cells(iRowNo, 74)
            sSpecificCapacity = .Cells(iRowNo, 75)
            sBrimfull = .Cells(iRowNo, 76)
            sFillHeight = .Cells(iRowNo, 77)
            sHeight = .Cells(iRowNo, 78)
            sWidth = .Cells(iRowNo, 79)
            sDepth = .Cells(iRowNo, 80)
            sCapToFitBottle = .Cells(iRowNo, 81)
            sPET_PreformNeck = .Cells(iRowNo, 82)
            sMachineToBeUsed = .Cells(iRowNo, 83)
            sCentres = .Cells(iRowNo, 84)
            sCavities = .Cells(iRowNo, 85)
            sDesignBrief = .Cells(iRowNo, 86)






'Generate and execute sql statement to import the excel rows to SQL Server table

conn.Execute "insert into dbo.TrackerStageA(Preprod, Description, Client, Initiated, F_Code," _
'" & Revised, Category, Sales_rep, Unit_order, Qty_required, S_O, Unit_Price, Amortisation," _
'" & Substrate_colour, Length, Material, Orifice, Foiling, Foil_Wad, Tube_Ø, Print_colours, Shoulder_col," _
'" & Cap_colour, Cap_Ø,  Cap_style, Cap_material, Cap_foil, Fuji, Other_info, Artwork, AW_order," _
'" & AW_received, Machine, Varnish, Filler, Status, Mould_no, Drawing_no, Extrusion_Trial, Foil_Trial," _
'" & Injection_Trial, Blowmoulding_Trial, Hinterkopf_Trial, Pad_Printing_Trial, Dubuit_Trial, _
'" & Moss_SS_Trial, Omso_SS_Trial, Omso_OffSet_Trial, K9_SS_Trial, Kamman_SS_Trial, Digital_Trial, R_Code, WorksOrder," _
'" & Due_SAESA, T1_E_D, T2_E_D, T3_E_D, T4_E_D, T5_E_D," _
'" & Pigment_MBatch_supplier_E_D, Pigment_MBatch_grade_E_D, Pigment_MBatch_percentage_E_D," _
'" & T1_F_I_B, T2_F_I_B, T3_F_I_B, T4_F_I_B, T5_F_I_B, Pigment_MBatch_supplier_F_I_B," _
'" & Pigment_MBatch_grade_F_I_B, ClosedDate," _
'" & Drawings, GrownSampleRequired, ArticleWeight_PreformWeight, SpecificCapacity, Brimfull, FillHeight, Height," _
'" & Width, Depth, CapToFitBottle, PET_PreformNeck, MachineToBeUsed, Centres, Cavities, DesignBrief) values ('" & sPreprod & "', '" & sDescription & "', " & _

'" & sClient & "', '" & sInitiated & "', '" & sF_Code & "', '" & sRevised & "', '" & sCategory & "', '" & sSales_rep & "', '" & sUnit_order & "', "& _

'" & sQty_required & "', '" & sS_O & "', '" & sUnit_Price & "', '" & sAmortisation & "', '" & sSubstrate_colour & "', '" & sLength & "', '" & sMaterial & "', '" & sOrifice & "', "& _

'" & sFoiling & ", '" & sFoil_Wad & "', '" & sTube_Ø & "', '" & sPrint_colours & "', '" & sShoulder_col & "', '" & sCap_colour & "', '" & sCap_Ø & "', '" & sCap_style & "', "& _

'" & sCap_material & "', '" & sCap_foil & "', '" & sFuji & "', '" & sOther_info & "', '" & sArtwork & "', '" & sAW_order & "', '" & sAW_received & "', '" & sMachine & "', "& _

'" & sVarnish & "', '" & sFiller & "', '" & sStatus & "', '" & sMould_no & "', '" & sDrawing_no & "', '" & sExtrusion_Trial & "', '" & sFoil_Trial & "', '" & sInjection_Trial & "', " & _

'" & sBlowmoulding_Trial & "', '" & sHinterkopf_Trial & "', '" & sPad_Printing_Trial & "',  '" & sDubuit_Trial & "', '" & sMoss_SS_Trial & "', '" & sOmso_SS_Trial & "', " & _

'" & sOmso_OffSet_Trial & "', '" & _ '" & sK9_SS_Trial & "', '" & sKamman_SS_Trial & "', '" & sDigital_Trial & "', '" & sR_Code & "', '" & sWorksOrder & "', '" & sDue_SAESA & "', " & _

'" & sT1_E_D & "', '" & sT2_E_D & "', '" & sT3_E_D & "', '" & _ '" & sT4_E_D & "', '" & sT5_E_D & "', '" & sPigment_MBatch_supplier_E_D & "', '" & sPigment_MBatch_grade_E_D & "', " & _

'" & sPigment_MBatch_percentage_E_D & "', '" & sT1_F_I_B & "', '" & sT2_F_I_B & "', '" & sT3_F_I_B & "', '" & sT4_F_I_B & "', '" & sT5_F_I_B & "', " &_

'" & sPigment_MBatch_supplier_F_I_B & "', '" & sPigment_MBatch_grade_F_I_B & "',  '" & sPigment_MBatch_percentage_F_I_B & "', '" & sClosedDate & "', '" & _

'" & sDrawings & "', '" & sGrownSampleRequired & "', '" & sArticleWeight_PreformWeight & "', '" & sSpecificCapacity & "', '" & sBrimfull & "', '" & sFillHeight & "', '" & sHeight & "', '" & _

'" & sWidth & "', '" & sDepth & "', '" & sCapToFitBottle & "', '" & sPET_PreformNeck & "', '" & sMachineToBeUsed & "', '" & sCentres & "', '" & sCavities & "', '" & sDesignBrief & "')"'
 
 iRowNo = iRowNo + 1
        Loop
        
        conn.Execute "EXEC dbo.MergeTrackerA"
            
        MsgBox "Tracker Data uploaded to SQL server."
            
        conn.Close
        Set conn = Nothing
            
    End With
End Sub
Private Sub CommandButton1_Click()

Cells.Select
Selection.Replace What:="FALSE", Replacement:="-", Lookat:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Dim conn As New ADODB.Connection
Dim iRowNo As Integer


Dim sPreprod, sDescription, sClient, sInitiated, sF_Code, sRevised, sCategory, sSales_rep, sUnit_order, sQty_required, sS_O, sUnit_Price, sAmortisation, sSubstrate_colour, sLength, sMaterial, sOrifice, sFoiling, sFoil_Wad, sTube_Ø, sPrint_colours, sShoulder_col, sCap_colour, sCap_Ø, sCap_style, sCap_material, sCap_foil, sFuji, sOther_info, sArtwork, sAW_order, sAW_received, sMachine, sVarnish, sFiller, sStatus, sMould_no, sDrawing_no, sExtrusion_Trial, sFoil_Trial, sInjection_Trial, sBlowmoulding_Trial, sHinterkopf_Trial, sPad_Printing_Trial, sDubuit_Trial, sMoss_SS_Trial, sOmso_SS_Trial, sOmso_OffSet_Trial, sK9_SS_Trial, sKamman_SS_Trial, sDigital_Trial, sR_Code, sWorksOrder, sDue_SAESA, sT1_E_D, sT2_E_D, sT3_E_D, sT4_E_D, sT5_E_D, sPigment_MBatch_supplier_E_D, sPigment_MBatch_grade_E_D, sPigment_MBatch_percentage_E_D, sT1_F_I_B, sT2_F_I_B, sT3_F_I_B, sT4_F_I_B, T5_F_I_B, sPigment_MBatch_supplier_F_I_B, sPigment_MBatch_grade_F_I_B, sPigment_MBatch_percentage_F_I_B, _
sClosedDate, sDrawings, sGrownSampleRequiredsArticleWeight_PreformWeight, sSpecificCapacity, sBrimfull, sFillHeight, sHeight, sWidth, sDepth, sCapToFitBottle, sPET_PreformNeck, sMachineToBeUsed, sCentres, sCavities, sDesignBrief As String

With Sheets("PP_Info")

'Open a connection to SQL Server
conn.Open "Provider=SQLOLEDB; Data Source=BP19DT015\SQLEXPRESS01;Initial Catalog=TrackerA;Integrated Security=SSPI;"

'Skip the header row
iRowNo = 2

'Loop until empty cell in Preprod
Do Until .Cells(iRowNo, 1) = ""
sPreprod = .Cells(iRowNo, 1)
sDescription = .Cells(iRowNo, 2)
sClient = .Cells(iRowNo, 3)
sInitiated = .Cells(iRowNo, 4)
sF_Code = .Cells(iRowNo, 5)
sRevised = .Cells(iRowNo, 6)
sCategory = .Cells(iRowNo, 7)
sSales_rep = .Cells(iRowNo, 8)
sUnit_order = .Cells(iRowNo, 9)
sQty_required = .Cells(iRowNo, 10)
sS_O = .Cells(iRowNo, 11)
sUnit_Price = .Cells(iRowNo, 12)
sAmortisation = .Cells(iRowNo, 13)
sSubstrate_colour = .Cells(iRowNo, 14)
sLength = .Cells(iRowNo, 15)
sMaterial = .Cells(iRowNo, 16)
sOrifice = .Cells(iRowNo, 17)
sFoiling = .Cells(iRowNo, 18)
sFoil_Wad = .Cells(iRowNo, 19)
sTube_Ø = .Cells(iRowNo, 20)
sPrint_colours = .Cells(iRowNo, 21)
sShoulder_col = .Cells(iRowNo, 22)
sCap_colour = .Cells(iRowNo, 23)
sCap_Ø = .Cells(iRowNo, 24)
sCap_style = .Cells(iRowNo, 25)
sCap_material = .Cells(iRowNo, 26)
sCap_foil = .Cells(iRowNo, 27)
sFuji = .Cells(iRowNo, 28)
sOther_info = .Cells(iRowNo, 29)
sArtwork = .Cells(iRowNo, 30)
sAW_order = .Cells(iRowNo, 31)
sAW_received = .Cells(iRowNo, 32)
sMachine = .Cells(iRowNo, 33)
sVarnish = .Cells(iRowNo, 34)
sFiller = .Cells(iRowNo, 35)
sStatus = .Cells(iRowNo, 36)
sMould_no = .Cells(iRowNo, 37)
sDrawing_no = .Cells(iRowNo, 38)
sExtrusion_Trial = .Cells(iRowNo, 39)
sFoil_Trial = .Cells(iRowNo, 40)
sInjection_Trial = .Cells(iRowNo, 41)
sBlowmoulding_Trial = .Cells(iRowNo, 42)
sHinterkopf_Trial = .Cells(iRowNo, 43)
sPad_Printing_Trial = .Cells(iRowNo, 44)
sDubuit_Trial = .Cells(iRowNo, 45)
sMoss_SS_Trial = .Cells(iRowNo, 46)
sOmso_SS_Trial = .Cells(iRowNo, 47)
sOmso_OffSet_Trial = .Cells(iRowNo, 48)
sK9_SS_Trial = .Cells(iRowNo, 49)
sKamman_SS_Trial = .Cells(iRowNo, 50)
sDigital_Trial = .Cells(iRowNo, 51)
sR_Code = .Cells(iRowNo, 52)
sWorksOrder = .Cells(iRowNo, 53)
sDue_SAESA = .Cells(iRowNo, 54)
sT1_E_D = .Cells(iRowNo, 55)
sT2_E_D = .Cells(iRowNo, 56)
sT3_E_D = .Cells(iRowNo, 57)
sT4_E_D = .Cells(iRowNo, 58)
sT5_E_D = .Cells(iRowNo, 59)
sPigment_MBatch_supplier_E_D = .Cells(iRowNo, 60)
sPigment_MBatch_grade_E_D = .Cells(iRowNo, 61)
sPigment_MBatch_percentage_E_D = .Cells(iRowNo, 62)
sT1_F_I_B = .Cells(iRowNo, 63)
sT2_F_I_B = .Cells(iRowNo, 64)
sT3_F_I_B = .Cells(iRowNo, 65)
sT4_F_I_B = .Cells(iRowNo, 66)
sT5_F_I_B = .Cells(iRowNo, 67)
sPigment_MBatch_supplier_F_I_B = .Cells(iRowNo, 68)
sPigment_MBatch_grade_F_I_B = .Cells(iRowNo, 69)
sPigment_MBatch_percentage_F_I_B = .Cells(iRowNo, 70)
sClosedDate = .Cells(iRowNo, 71)
sDrawings = .Cells(iRowNo, 72)
sGrownSampleRequired = .Cells(iRowNo, 73)
sArticleWeight_PreformWeight = .Cells(iRowNo, 74)
sSpecificCapacity = .Cells(iRowNo, 75)
sBrimfull = .Cells(iRowNo, 76)
sFillHeight = .Cells(iRowNo, 77)
sHeight = .Cells(iRowNo, 78)
sWidth = .Cells(iRowNo, 79)
sDepth = .Cells(iRowNo, 80)
sCapToFitBottle = .Cells(iRowNo, 81)
sPET_PreformNeck = .Cells(iRowNo, 82)
sMachineToBeUsed = .Cells(iRowNo, 83)
sCentres = .Cells(iRowNo, 84)
sCavities = .Cells(iRowNo, 85)
sDesignBrief = .Cells(iRowNo, 86)






'Generate and execute sql statement to import the excel rows to SQL Server table

conn.Execute "insert into dbo.TrackerStageA(Preprod, Description, Client, Initiated, F_Code," _
'" & Revised, Category, Sales_rep, Unit_order, Qty_required, S_O, Unit_Price, Amortisation," _
'" & Substrate_colour, Length, Material, Orifice, Foiling, Foil_Wad, Tube_Ø, Print_colours, Shoulder_col," _
'" & Cap_colour, Cap_Ø, Cap_style, Cap_material, Cap_foil, Fuji, Other_info, Artwork, AW_order," _
'" & AW_received, Machine, Varnish, Filler, Status, Mould_no, Drawing_no, Extrusion_Trial, Foil_Trial," _
'" & Injection_Trial, Blowmoulding_Trial, Hinterkopf_Trial, Pad_Printing_Trial, Dubuit_Trial, _
'" & Moss_SS_Trial, Omso_SS_Trial, Omso_OffSet_Trial, K9_SS_Trial, Kamman_SS_Trial, Digital_Trial, R_Code, WorksOrder," _
'" & Due_SAESA, T1_E_D, T2_E_D, T3_E_D, T4_E_D, T5_E_D," _
'" & Pigment_MBatch_supplier_E_D, Pigment_MBatch_grade_E_D, Pigment_MBatch_percentage_E_D," _
'" & T1_F_I_B, T2_F_I_B, T3_F_I_B, T4_F_I_B, T5_F_I_B, Pigment_MBatch_supplier_F_I_B," _
'" & Pigment_MBatch_grade_F_I_B, ClosedDate," _
'" & Drawings, GrownSampleRequired, ArticleWeight_PreformWeight, SpecificCapacity, Brimfull, FillHeight, Height," _
'" & Width, Depth, CapToFitBottle, PET_PreformNeck, MachineToBeUsed, Centres, Cavities, DesignBrief) values ('" & sPreprod & "', '" & sDescription & "', " & _

'" & sClient & "', '" & sInitiated & "', '" & sF_Code & "', '" & sRevised & "', '" & sCategory & "', '" & sSales_rep & "', '" & sUnit_order & "', "& _

'" & sQty_required & "', '" & sS_O & "', '" & sUnit_Price & "', '" & sAmortisation & "', '" & sSubstrate_colour & "', '" & sLength & "', '" & sMaterial & "', '" & sOrifice & "', "& _

'" & sFoiling & ", '" & sFoil_Wad & "', '" & sTube_Ø & "', '" & sPrint_colours & "', '" & sShoulder_col & "', '" & sCap_colour & "', '" & sCap_Ø & "', '" & sCap_style & "', "& _

'" & sCap_material & "', '" & sCap_foil & "', '" & sFuji & "', '" & sOther_info & "', '" & sArtwork & "', '" & sAW_order & "', '" & sAW_received & "', '" & sMachine & "', "& _

'" & sVarnish & "', '" & sFiller & "', '" & sStatus & "', '" & sMould_no & "', '" & sDrawing_no & "', '" & sExtrusion_Trial & "', '" & sFoil_Trial & "', '" & sInjection_Trial & "', " & _

'" & sBlowmoulding_Trial & "', '" & sHinterkopf_Trial & "', '" & sPad_Printing_Trial & "', '" & sDubuit_Trial & "', '" & sMoss_SS_Trial & "', '" & sOmso_SS_Trial & "', " & _

'" & sOmso_OffSet_Trial & "', '" & _ '" & sK9_SS_Trial & "', '" & sKamman_SS_Trial & "', '" & sDigital_Trial & "', '" & sR_Code & "', '" & sWorksOrder & "', '" & sDue_SAESA & "', " & _

'" & sT1_E_D & "', '" & sT2_E_D & "', '" & sT3_E_D & "', '" & _ '" & sT4_E_D & "', '" & sT5_E_D & "', '" & sPigment_MBatch_supplier_E_D & "', '" & sPigment_MBatch_grade_E_D & "', " & _

'" & sPigment_MBatch_percentage_E_D & "', '" & sT1_F_I_B & "', '" & sT2_F_I_B & "', '" & sT3_F_I_B & "', '" & sT4_F_I_B & "', '" & sT5_F_I_B & "', " &_

'" & sPigment_MBatch_supplier_F_I_B & "', '" & sPigment_MBatch_grade_F_I_B & "', '" & sPigment_MBatch_percentage_F_I_B & "', '" & sClosedDate & "', '" & _

'" & sDrawings & "', '" & sGrownSampleRequired & "', '" & sArticleWeight_PreformWeight & "', '" & sSpecificCapacity & "', '" & sBrimfull & "', '" & sFillHeight & "', '" & sHeight & "', '" & _

'" & sWidth & "', '" & sDepth & "', '" & sCapToFitBottle & "', '" & sPET_PreformNeck & "', '" & sMachineToBeUsed & "', '" & sCentres & "', '" & sCavities & "', '" & sDesignBrief & "')"'

iRowNo = iRowNo + 1
Loop

conn.Execute "EXEC dbo.MergeTrackerA"

MsgBox "Tracker Data uploaded to SQL server."

conn.Close
Set conn = Nothing

End With
End Sub
Click to expand...
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
Looking at the lines that are commented out, the first 12 should start with & " instead of " & while the rest below that should just be &
 
Upvote 0
VBA Code: 
conn.Execute "insert into dbo.TrackerStageA(Preprod, Description, Client, Initiated, F_Code," _
 & " Revised, Category, Sales_rep, Unit_order, Qty_required, S_O, Unit_Price, Amortisation," _
 & " Substrate_colour, Length, Material, Orifice, Foiling, Foil_Wad, Tube_Ø, Print_colours, Shoulder_col," _
 & " Cap_colour, Cap_Ø,  Cap_style, Cap_material, Cap_foil, Fuji, Other_info, Artwork, AW_order," _
 & " AW_received, Machine, Varnish, Filler, Status, Mould_no, Drawing_no, Extrusion_Trial, Foil_Trial," _
 & " Injection_Trial, Blowmoulding_Trial, Hinterkopf_Trial, Pad_Printing_Trial, Dubuit_Trial, " _
 & " Moss_SS_Trial, Omso_SS_Trial, Omso_OffSet_Trial, K9_SS_Trial, Kamman_SS_Trial, Digital_Trial, R_Code, WorksOrder," _
 & " Due_SAESA, T1_E_D, T2_E_D, T3_E_D, T4_E_D, T5_E_D," _
 & " Pigment_MBatch_supplier_E_D, Pigment_MBatch_grade_E_D, Pigment_MBatch_percentage_E_D," _
 & " T1_F_I_B, T2_F_I_B, T3_F_I_B, T4_F_I_B, T5_F_I_B, Pigment_MBatch_supplier_F_I_B," _
 & " Pigment_MBatch_grade_F_I_B, ClosedDate," _
 & " Drawings, GrownSampleRequired, ArticleWeight_PreformWeight, SpecificCapacity, Brimfull, FillHeight, Height," _
 & " Width, Depth, CapToFitBottle, PET_PreformNeck, MachineToBeUsed, Centres, Cavities, DesignBrief) values ('" & sPreprod & "', '" & sDescription & "', " & _
'" & sClient & "', '" & sInitiated & "', '" & sF_Code & "', '" & sRevised & "', '" & sCategory & "', '" & sSales_rep & "', '" & sUnit_order & "', " & _
Code:
 
Upvote 0
yannine said:
VBA Code: 
conn.Execute "insert into dbo.TrackerStageA(Preprod, Description, Client, Initiated, F_Code," _
 & " Revised, Category, Sales_rep, Unit_order, Qty_required, S_O, Unit_Price, Amortisation," _
 & " Substrate_colour, Length, Material, Orifice, Foiling, Foil_Wad, Tube_Ø, Print_colours, Shoulder_col," _
 & " Cap_colour, Cap_Ø,  Cap_style, Cap_material, Cap_foil, Fuji, Other_info, Artwork, AW_order," _
 & " AW_received, Machine, Varnish, Filler, Status, Mould_no, Drawing_no, Extrusion_Trial, Foil_Trial," _
 & " Injection_Trial, Blowmoulding_Trial, Hinterkopf_Trial, Pad_Printing_Trial, Dubuit_Trial, " _
 & " Moss_SS_Trial, Omso_SS_Trial, Omso_OffSet_Trial, K9_SS_Trial, Kamman_SS_Trial, Digital_Trial, R_Code, WorksOrder," _
 & " Due_SAESA, T1_E_D, T2_E_D, T3_E_D, T4_E_D, T5_E_D," _
 & " Pigment_MBatch_supplier_E_D, Pigment_MBatch_grade_E_D, Pigment_MBatch_percentage_E_D," _
 & " T1_F_I_B, T2_F_I_B, T3_F_I_B, T4_F_I_B, T5_F_I_B, Pigment_MBatch_supplier_F_I_B," _
 & " Pigment_MBatch_grade_F_I_B, ClosedDate," _
 & " Drawings, GrownSampleRequired, ArticleWeight_PreformWeight, SpecificCapacity, Brimfull, FillHeight, Height," _
 & " Width, Depth, CapToFitBottle, PET_PreformNeck, MachineToBeUsed, Centres, Cavities, DesignBrief) values ('" & sPreprod & "', '" & sDescription & "', " & _
'" & sClient & "', '" & sInitiated & "', '" & sF_Code & "', '" & sRevised & "', '" & sCategory & "', '" & sSales_rep & "', '" & sUnit_order & "', " & _
Code:
Click to expand...
Hi Jason, Thanks so much for your reply. I changed the '" & to &" but now get a compile error. I'm obviously doing something wrong and just can't see it. Regards, C:unsure:
 
Upvote 0
You need to do the other lines as well, as I said before
jasonb75 said:
the rest below that should just be &
Click to expand...
but I can see from your post that you haven't corrected those lines.

edit:- I've just noticed another error as well, at the end of the last line that you ave already edited and the lines below it, you have & "', " & _ which should b & "', '" & _ (need to add an apostrophe before the closing double quote).
 
Upvote 0
jasonb75 said:
You need to do the other lines as well, as I said before

but I can see from your post that you haven't corrected those lines.

edit:- I've just noticed another error as well, at the end of the last line that you ave already edited and the lines below it, you have & "', " & _ which should b & "', '" & _ (need to add an apostrophe before the closing double quote).
Click to expand...
Thanks so much for all your help, Jason!
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,743
Messages
6,174,244
Members
452,553
Latest member
red83

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top