'For Loop' Does Not Work

[violette]

I have five sheets named 'Yearly,Q1,Q2,Q3,Q4'. currently my code only update one worksheet at one time into the mySql database. i want it to get all worksheets to be done at one go. i've tried to implement 'For loop' but it just run on the active worksheet only. is there anything need to be added into my coding to make it works? here's my code;

For Each wksSheet In wbkFirst.Worksheets
    
    Do While IsEmpty(Cells(lastRowCom, "B").Value) = False
    
        stockCodeExist = False
       stockCodeCom = Trim(Cells(lastRowCom, "B").Value)
       marketCapCom = Trim(Cells(lastRowCom, "C").Value)
       revenueCom = Trim(Cells(lastRowCom, "D").Value)
       TotLiability = Trim(Cells(lastRowCom, "E").Value)
       curLiability = Trim(Cells(lastRowCom, "F").Value)
       tradeReceivable = Trim(Cells(lastRowCom, "G").Value)
       inventory = Trim(Cells(lastRowCom, "H").Value)
       shareholderEqyCom = Trim(Cells(lastRowCom, "I").Value)
       totLiabToSE = Trim(Cells(lastRowCom, "J").Value)
       currentAss = Trim(Cells(lastRowCom, "K").Value)
       fixedAsset = Trim(Cells(lastRowCom, "L").Value)
       operatingExpenses = Trim(Cells(lastRowCom, "M").Value)
       ClosingPrice = Trim(Cells(lastRowCom, "N").Value)
       priceEarningRatio = Trim(Cells(lastRowCom, "O").Value)
       earningPerShare = Trim(Cells(lastRowCom, "P").Value)
       salesToTotalAss = Trim(Cells(lastRowCom, "Q").Value)
       totalAss = Trim(Cells(lastRowCom, "R").Value)
       operatingCashFlow = Trim(Cells(lastRowCom, "S").Value)
       peRatio = Trim(Cells(lastRowCom, "T").Value)
       altmanZ = Trim(Cells(lastRowCom, "U").Value)
       cashNeqvalent = Trim(Cells(lastRowCom, "V").Value)
       EbitCom = Trim(Cells(lastRowCom, "W").Value)
       sharesOutstdgCom = Trim(Cells(lastRowCom, "X").Value)
       netAttributableCom = Trim(Cells(lastRowCom, "Y").Value)
       incStock = Trim(Cells(lastRowCom, "Z").Value)
       netInvest = Trim(Cells(lastRowCom, "AA").Value)
       retainedPOL = Trim(Cells(lastRowCom, "AB").Value)
       ReOTA = Trim(Cells(lastRowCom, "AC").Value)
       ReOE = Trim(Cells(lastRowCom, "AD").Value)
       AvergShares = Trim(Cells(lastRowCom, "AE").Value)
       netPOL = Trim(Cells(lastRowCom, "AF").Value)
       DebtEqy = Trim(Cells(lastRowCom, "AG").Value)
       paidUpCapital = Trim(Cells(lastRowCom, "AH").Value)
       fiyeMonth = Trim(Cells(lastRowCom, "AI").Value)
       yearIdCom = Cells(1, 2)
       periodIdCom = Cells(1, 3)

        
        'check company financial exist
        Set rs = New ADODB.Recordset
        sqlStr = "SELECT year_id, period_id, stock_code FROM company_financial WHERE year_id = '" & yearIdCom & "' AND period_id = '" & periodIdCom & "' AND stock_code = '" & stockCodeCom & "' "
        rs.Open sqlStr, conn, adOpenStatic

            If rs.EOF Then
                'record not exist in DB
                recordExistCom = False
            Else
                stockCodeCom = rs.Fields("stock_code")
                recordExistCom = True
            End If

        rs.Close
        Set rs = Nothing


       If recordExistCom = False Then
           'insert new company
           If stockCodeCom <> "" Then
             
               sqlStr = "INSERT INTO company_financial "
               sqlStr = sqlStr & "(year_id, period_id, stock_code, receivable, revenue, total_liabilities, shareholders_equity, total_liabilities_to_shareholders_equity, current_liabilities, total_current_assets, net_attributable, inventories, fixed_assets, operating_cost, net_cash_operation, price_close, pe, eps, pe_relative_sector, altman_z_score, paid_up_capital, net_profit_or_loss, dec_stock, dec_debtors, inc_creditors, net_investments, cash_and_equivalents, retained_profit_or_loss, ebit, mkt_cap, sales_to_assets, rota, roe, debt_to_equity, average_shares, total_assets, shares_outstanding, report_period_end_mth,"
               sqlStr = sqlStr & "created_by,created_date)"
               sqlStr = sqlStr & " VALUES "
               sqlStr = sqlStr & " ('" & yearIdCom & "', '" & periodIdCom & "', '" & stockCodeCom & "', '" & tradeReceivable & "', '" & revenueCom & "', '" & TotLiability & "', '" & shareholderEqyCom & "', '" & totLiabToSE & "', '" & curLiability & "', '" & currentAss & "', '" & netAttributableCom & "', '" & inventory & "', '" & fixedAsset & "', '" & operatingExpenses & "', '" & operatingCashFlow & "', '" & ClosingPrice & "', '" & priceEarningRatio & "', '" & earningPerShare & "', '" & peRatio & "', '" & altmanZ & "', '" & paidUpCapital & "', '" & netPOL & "', '" & incStock & "', '" & netInvest & "', '" & netInvest & "', '" & netInvest & "', '" & cashNeqvalent & "', '" & retainedPOL & "', '" & EbitCom & "', '" & marketCapCom & "', '" & salesToTotalAss & "', '" & ReOTA & "', '" & ReOE & "', '" & DebtEqy & "', '" & AvergShares & "', '" & totalAss & "', '" & sharesOutstdgCom & "', '" & fiyeMonth & "',"
               sqlStr = sqlStr & "'fistconv','" & Format(Now(), "yyyy-MM-dd HH:mm") & "')"
               
               conn.Execute sqlStr
           End If
        
             lastRow = lastRow + 1
             Range("C3").Value = lastRow - 5
       
       Else
       
          sqlStr = "UPDATE company_financial SET "
          sqlStr = sqlStr & " receivable = '" & CDbl(tradeReceivable) & "',"
          sqlStr = sqlStr & " revenue = '" & CDbl(revenueCom) & "',"
          sqlStr = sqlStr & " total_liabilities = '" & CDbl(TotLiability) & "',"
          sqlStr = sqlStr & " shareholders_equity = '" & CDbl(shareholderEqyCom) & "',"
          sqlStr = sqlStr & " total_liabilities_to_shareholders_equity = '" & CDbl(totLiabToSE) & "',"
          sqlStr = sqlStr & " current_liabilities = '" & CDbl(curLiability) & "',"
          sqlStr = sqlStr & " total_current_assets = '" & currentAss & "',"
          sqlStr = sqlStr & " net_attributable = '" & netAttributableCom & "',"
          sqlStr = sqlStr & " inventories = '" & inventory & "',"
          sqlStr = sqlStr & " fixed_assets = '" & fixedAsset & "',"
          sqlStr = sqlStr & " operating_cost = '" & operatingExpenses & "',"
          sqlStr = sqlStr & " net_cash_operation = '" & operatingCashFlow & "',"
          sqlStr = sqlStr & " price_close = '" & ClosingPrice & "',"
          sqlStr = sqlStr & " pe = '" & priceEarningRatio & "',"
          sqlStr = sqlStr & " eps = '" & earningPerShare & "',"
          sqlStr = sqlStr & " pe_relative_sector = '" & peRatio & "',"
          sqlStr = sqlStr & " altman_z_score = '" & altmanZ & "',"
          sqlStr = sqlStr & " paid_up_capital = '" & paidUpCapital & "',"
          sqlStr = sqlStr & " net_profit_or_loss = '" & netPOL & "',"
          sqlStr = sqlStr & " dec_stock = '" & incStock & "',"
          sqlStr = sqlStr & " dec_debtors = '" & netInvest & "',"
          sqlStr = sqlStr & " inc_creditors = '" & netInvest & "',"
          sqlStr = sqlStr & " net_investments = '" & netInvest & "',"
          sqlStr = sqlStr & " cash_and_equivalents = '" & cashNeqvalent & "',"
          sqlStr = sqlStr & " retained_profit_or_loss = '" & retainedPOL & "',"
          sqlStr = sqlStr & " ebit = '" & EbitCom & "',"
          sqlStr = sqlStr & " mkt_cap = '" & marketCapCom & "',"
          sqlStr = sqlStr & " sales_to_assets = '" & salesToTotalAss & "',"
          sqlStr = sqlStr & " rota = '" & ReOTA & "',"
          sqlStr = sqlStr & " roe = '" & ReOE & "',"
          sqlStr = sqlStr & " debt_to_equity = '" & DebtEqy & "',"
          sqlStr = sqlStr & " average_shares = '" & AvergShares & "',"
          sqlStr = sqlStr & " total_assets = '" & totalAss & "',"
          sqlStr = sqlStr & " shares_outstanding = '" & sharesOutstdgCom & "',"
          sqlStr = sqlStr & " report_period_end_mth = '" & fiyeMonth & "',"

          sqlStr = sqlStr & " update_by = 'fistconv',"
          sqlStr = sqlStr & " update_date = '" & Format(Now(), "yyyy-MM-dd HH:mm") & "'"
          sqlStr = sqlStr & " WHERE year_id = '" & yearIdCom & "' AND period_id = '" & periodIdCom & "' AND stock_code = '" & stockCodeCom & "'"
          
          Debug.Print sqlStr
          
          conn.Execute sqlStr
    
       End If
    
        lastRowCom = lastRowCom + 1
        
        Range("E3").Value = lastRowCom - 5
        
    Loop
     Next wksSheet

thanks in advance for your help guys :)

[BitBlt]

How are you instantiating wbkFirst? If it is not a Workbook then you can't really get to the worksheets inside.

One technique I've used is to create a variable and set it equal to Excel.ActiveWorkbook. Then you can do a for loop through it.

Here's some sample code:

Dim book1 As Workbook

Set book1 = Excel.ActiveWorkbook

For Each wksSheet In book1.Worksheets
    Debug.Print wksSheet.Name
Next wksSheet

Of course, you will need to put some code into the "for" loop to actually do some work, but this was for illustration purposes only.

Hope this helps! Good luck!

[violette]

well, there is only one workbook thus it will be the active workbook automatically once it opened. im looking for the code that will makes my 'for' loop do the work inside it.

[cguan_77]

hi violette, try isolating your problem.

For Each wksSheet In wbkFirst.Worksheets

Msgbox wkSheet.Name

Next wksSheet

check if it will loop through your worksheets and display it's name.

it it does loop through the WS, then check your do while loop or your if then else statement.

[violette]

hmm..it does prompt message box with each worksheet name...

[violette]

i've tried using if else by setting the worksheet name as the condition, and copied the 'do while loop' into the body part but it only do insert and update data for the active sheet only. >.<

[BitBlt]

I think it is because you are not referencing the Cells collection for the sheet you are iterating through. Here's some code to test with to see if that's the case with you:

For Each wksSheet In ThisWorkbook.Worksheets
    Debug.Print wksSheet.Name
    Debug.Print Cells(1, "a").Value
    Debug.Print Cells(2, "a").Value
    Debug.Print Cells(3, "a").Value
    Debug.Print wksSheet.Cells(1, "a").Value
    Debug.Print wksSheet.Cells(2, "a").Value
    Debug.Print wksSheet.Cells(3, "a").Value
Next wksSheet

[cguan_77]

hi violette, try the suggestion of BitBit. I think this will do the trick.

For Each wksSheet In wbkFirst.Worksheets

  'as BitBit suggested do this 
  stockCodeCom = Trim(wksSheet.Cells(lastRowCom, "B").Value)
  marketCapCom = Trim(wksSheet.Cells(lastRowCom, "C").Value)

Next wksSheet

check if it will work.. Good luck!

[violette]

I think it is because you are not referencing the Cells collection for the sheet you are iterating through. Here's some code to test with to see if that's the case with you:

For Each wksSheet In ThisWorkbook.Worksheets
    Debug.Print wksSheet.Name
    Debug.Print Cells(1, "a").Value
    Debug.Print Cells(2, "a").Value
    Debug.Print Cells(3, "a").Value
    Debug.Print wksSheet.Cells(1, "a").Value
    Debug.Print wksSheet.Cells(2, "a").Value
    Debug.Print wksSheet.Cells(3, "a").Value
Next wksSheet

hello BitBlt, i've tried ur way and it does print the value according to the worksheet. however, when i replace it to my code, it does not work. only the value from the active worksheet is updated into the database.

[violette]

hi violette, try the suggestion of BitBit. I think this will do the trick.

For Each wksSheet In wbkFirst.Worksheets

  'as BitBit suggested do this 
  stockCodeCom = Trim(wksSheet.Cells(lastRowCom, "B").Value)
  marketCapCom = Trim(wksSheet.Cells(lastRowCom, "C").Value)

Next wksSheet

check if it will work.. Good luck!

i've done this as well. still give the same result. >_<

[BitBlt]

Post your modified code. Saying "it doesn't work" tells us nothing. It just means there's something else going on.

[violette]

hi all. i have found the solution ady. the main reason why it doesnt work is because i did not reset the lastRowCom so thats why it didnt update the next sheet into the database. here is the modified version of my code that works.

For Each wksSheet In wbkFirst.Worksheets
            lastRow = 5
            lastRowCom = 5
          With wksSheet
            
            Do While IsEmpty(wksSheet.Cells(lastRowCom, "B").Value) = False
    
            stockCodeExist = False
            'Sheet Yearly
            stockCodeCom = Trim(wksSheet.Cells(lastRowCom, "B").Value)
            marketCapCom = Trim(wksSheet.Cells(lastRowCom, "C").Value)
            revenueCom = Trim(wksSheet.Cells(lastRowCom, "D").Value)
            TotLiability = Trim(wksSheet.Cells(lastRowCom, "E").Value)
            curLiability = Trim(wksSheet.Cells(lastRowCom, "F").Value)
            tradeReceivable = Trim(wksSheet.Cells(lastRowCom, "G").Value)
            inventory = Trim(wksSheet.Cells(lastRowCom, "H").Value)
            shareholderEqyCom = Trim(wksSheet.Cells(lastRowCom, "I").Value)
            totLiabToSE = Trim(wksSheet.Cells(lastRowCom, "J").Value)
            currentAss = Trim(wksSheet.Cells(lastRowCom, "K").Value)
            fixedAsset = Trim(wksSheet.Cells(lastRowCom, "L").Value)
            operatingExpenses = Trim(wksSheet.Cells(lastRowCom, "M").Value)
            ClosingPrice = Trim(wksSheet.Cells(lastRowCom, "N").Value)
            priceEarningRatio = Trim(wksSheet.Cells(lastRowCom, "O").Value)
            earningPerShare = Trim(wksSheet.Cells(lastRowCom, "P").Value)
            salesToTotalAss = Trim(wksSheet.Cells(lastRowCom, "Q").Value)
            totalAss = Trim(wksSheet.Cells(lastRowCom, "R").Value)
            operatingCashFlow = Trim(wksSheet.Cells(lastRowCom, "S").Value)
            peRatio = Trim(wksSheet.Cells(lastRowCom, "T").Value)
            altmanZ = Trim(wksSheet.Cells(lastRowCom, "U").Value)
            cashNeqvalent = Trim(wksSheet.Cells(lastRowCom, "V").Value)
            EbitCom = Trim(wksSheet.Cells(lastRowCom, "W").Value)
            sharesOutstdgCom = Trim(wksSheet.Cells(lastRowCom, "X").Value)
            netAttributableCom = Trim(wksSheet.Cells(lastRowCom, "Y").Value)
            incStock = Trim(wksSheet.Cells(lastRowCom, "Z").Value)
            netInvest = Trim(wksSheet.Cells(lastRowCom, "AA").Value)
            retainedPOL = Trim(wksSheet.Cells(lastRowCom, "AB").Value)
            ReOTA = Trim(wksSheet.Cells(lastRowCom, "AC").Value)
            ReOE = Trim(wksSheet.Cells(lastRowCom, "AD").Value)
            AvergShares = Trim(wksSheet.Cells(lastRowCom, "AE").Value)
            netPOL = Trim(wksSheet.Cells(lastRowCom, "AF").Value)
            DebtEqy = Trim(wksSheet.Cells(lastRowCom, "AG").Value)
            paidUpCapital = Trim(wksSheet.Cells(lastRowCom, "AH").Value)
            fiyeMonth = Trim(wksSheet.Cells(lastRowCom, "AI").Value)
            yearIdCom = wksSheet.Cells(1, 2)
            periodIdCom = wksSheet.Cells(1, 3)
            
            'check company financial exist
            Set rs = New ADODB.Recordset
            sqlStr = "SELECT year_id, period_id, stock_code FROM company_financial WHERE year_id = '" & yearIdCom & "' AND period_id = '" & periodIdCom & "' AND stock_code = '" & stockCodeCom & "' "
            rs.Open sqlStr, conn, adOpenStatic

            If rs.EOF Then
                'record not exist in DB
                recordExistCom = False
            Else
                yearIdCom = rs.Fields("year_id")
                periodIdCom = rs.Fields("period_id")
                stockCodeCom = rs.Fields("stock_code")
                recordExistCom = True
            End If

            rs.Close
            Set rs = Nothing


            If recordExistCom = False Then
                'insert new company
                If stockCodeCom <> "" Then
             
                    sqlStr = "INSERT INTO company_financial "
                    sqlStr = sqlStr & "(year_id, period_id, stock_code, receivable, revenue, total_liabilities, shareholders_equity, total_liabilities_to_shareholders_equity, current_liabilities, total_current_assets, net_attributable, inventories, fixed_assets, operating_cost, net_cash_operation, price_close, pe, eps, pe_relative_sector, altman_z_score, "
                    sqlStr = sqlStr & "paid_up_capital, net_profit_or_loss, dec_stock, dec_debtors, inc_creditors, net_investments, cash_and_equivalents, retained_profit_or_loss, ebit, mkt_cap, sales_to_assets, rota, roe, debt_to_equity, average_shares, total_assets, shares_outstanding, report_period_end_mth,"
                    sqlStr = sqlStr & "created_by,created_date)"
                    sqlStr = sqlStr & " VALUES "
                    sqlStr = sqlStr & " ('" & yearIdCom & "', '" & periodIdCom & "', '" & stockCodeCom & "', '" & CDbl(tradeReceivable) & "', '" & CDbl(revenueCom) & "', '" & CDbl(TotLiability) & "', '" & CDbl(shareholderEqyCom) & "', '" & CDbl(totLiabToSE) & "', '" & CDbl(curLiability) & "', '" & CDbl(currentAss) & "', '" & CDbl(netAttributableCom) & "', '" & CDbl(inventory) & "', '" & CDbl(fixedAsset) & "', '" & CDbl(operatingExpenses) & "', '" & CDbl(operatingCashFlow) & "', '" & CDbl(ClosingPrice) & "', '" & CDbl(priceEarningRatio) & "', '" & CDbl(earningPerShare) & "', '" & CDbl(peRatio) & "', '" & CDbl(altmanZ) & "',"
                    sqlStr = sqlStr & "'" & CDbl(paidUpCapital) & "', '" & CDbl(netPOL) & "', '" & CDbl(incStock) & "', '" & CDbl(netInvest) & "', '" & CDbl(netInvest) & "', '" & CDbl(netInvest) & "', '" & CDbl(cashNeqvalent) & "', '" & CDbl(retainedPOL) & "', '" & CDbl(EbitCom) & "', '" & CDbl(marketCapCom) & "', '" & CDbl(salesToTotalAss) & "', '" & CDbl(ReOTA) & "', '" & CDbl(ReOE) & "', '" & CDbl(DebtEqy) & "', '" & CDbl(AvergShares) & "', '" & CDbl(totalAss) & "', '" & CDbl(sharesOutstdgCom) & "', '" & fiyeMonth & "',"
                    sqlStr = sqlStr & "'fistconv','" & Format(Now(), "yyyy-MM-dd HH:mm") & "')"
               
                    conn.Execute sqlStr
                    lastRow = lastRow + 1
                End If
                    Range("C3").Value = lastRow - 5
       
            Else
       
                sqlStr = "UPDATE company_financial SET "
                sqlStr = sqlStr & " receivable = '" & CDbl(tradeReceivable) & "',"
                sqlStr = sqlStr & " revenue = '" & CDbl(revenueCom) & "',"
                sqlStr = sqlStr & " total_liabilities = '" & CDbl(TotLiability) & "',"
                sqlStr = sqlStr & " shareholders_equity = '" & CDbl(shareholderEqyCom) & "',"
                sqlStr = sqlStr & " total_liabilities_to_shareholders_equity = '" & CDbl(totLiabToSE) & "',"
                sqlStr = sqlStr & " current_liabilities = '" & CDbl(curLiability) & "',"
                sqlStr = sqlStr & " total_current_assets = '" & CDbl(currentAss) & "',"
                sqlStr = sqlStr & " net_attributable = '" & CDbl(netAttributableCom) & "',"
                sqlStr = sqlStr & " inventories = '" & CDbl(inventory) & "',"
                sqlStr = sqlStr & " fixed_assets = '" & CDbl(fixedAsset) & "',"
                sqlStr = sqlStr & " operating_cost = '" & CDbl(operatingExpenses) & "',"
                sqlStr = sqlStr & " net_cash_operation = '" & CDbl(operatingCashFlow) & "',"
                sqlStr = sqlStr & " price_close = '" & CDbl(ClosingPrice) & "',"
                sqlStr = sqlStr & " pe = '" & CDbl(priceEarningRatio) & "',"
                sqlStr = sqlStr & " eps = '" & CDbl(earningPerShare) & "',"
                sqlStr = sqlStr & " pe_relative_sector = '" & CDbl(peRatio) & "',"
                sqlStr = sqlStr & " altman_z_score = '" & CDbl(altmanZ) & "',"
                sqlStr = sqlStr & " paid_up_capital = '" & CDbl(paidUpCapital) & "',"
                sqlStr = sqlStr & " net_profit_or_loss = '" & CDbl(netPOL) & "',"
                sqlStr = sqlStr & " dec_stock = '" & CDbl(incStock) & "',"
                sqlStr = sqlStr & " dec_debtors = '" & CDbl(netInvest) & "',"
                sqlStr = sqlStr & " inc_creditors = '" & CDbl(netInvest) & "',"
                sqlStr = sqlStr & " net_investments = '" & CDbl(netInvest) & "',"
                sqlStr = sqlStr & " cash_and_equivalents = '" & CDbl(cashNeqvalent) & "',"
                sqlStr = sqlStr & " retained_profit_or_loss = '" & CDbl(retainedPOL) & "',"
                sqlStr = sqlStr & " ebit = '" & CDbl(EbitCom) & "',"
                sqlStr = sqlStr & " mkt_cap = '" & CDbl(marketCapCom) & "',"
                sqlStr = sqlStr & " sales_to_assets = '" & CDbl(salesToTotalAss) & "',"
                sqlStr = sqlStr & " rota = '" & CDbl(ReOTA) & "',"
                sqlStr = sqlStr & " roe = '" & CDbl(ReOE) & "',"
                sqlStr = sqlStr & " debt_to_equity = '" & CDbl(DebtEqy) & "',"
                sqlStr = sqlStr & " average_shares = '" & CDbl(AvergShares) & "',"
                sqlStr = sqlStr & " total_assets = '" & CDbl(totalAss) & "',"
                sqlStr = sqlStr & " shares_outstanding = '" & CDbl(sharesOutstdgCom) & "',"
                sqlStr = sqlStr & " report_period_end_mth = '" & fiyeMonth & "',"

                sqlStr = sqlStr & " update_by = 'fistconv',"
                sqlStr = sqlStr & " update_date = '" & Format(Now(), "yyyy-MM-dd HH:mm") & "'"
                sqlStr = sqlStr & " WHERE year_id = '" & yearIdCom & "' AND period_id = '" & periodIdCom & "' AND stock_code = '" & stockCodeCom & "'"
          
                Debug.Print sqlStr
          
                conn.Execute sqlStr
                lastRowCom = lastRowCom + 1
            End If
                
                Range("E3").Value = lastRowCom - 5

            Loop
            
            End With
        Next wksSheet