+ Reply to Thread
Results 1 to 2 of 2

whenever i download data from my excelsheet it creates the duplicate rows and columns

  1. #1
    Forum Contributor
    Join Date
    03-01-2014
    Location
    Mysore
    MS-Off Ver
    Excel 2007
    Posts
    379

    whenever i download data from my excelsheet it creates the duplicate rows and columns

    please help me out i am stuck here here i wanted to download data from excel to mysql but it is not downloading it insisted of it creating duplicate rows and columns please let me know i am new for this....

    Sub downloadData()
    Dim strSQL As New ADODB.Connection
    Dim dbconn As New ADODB.Connection
    Dim i, prdTblName
    prdTblName = "tblProd_" & projCode & "_" & batchCode
    Dim qryStr, dataStr As String

    qryStr = "SELECT * FROM tblbatch_headers where idBatch " & batchID & "order by col_seq asc"

    strSQL = "INSERT INTO [ODBC;DSN=mysql32;].wds " _
    & "Select * FROM ;"

    Dim rs As New ADODB.Recordset
    dbconn.Open strConn
    rs.Open qryStr, dbconn, 3, 1
    i = 0
    Do While rs.EOF = False

    i = i + 1
    If i = 1 Then
    dataStr = rs("tblColName")
    Else
    dataStr = dataStr & ", " & rs("tblColName")
    End If
    rs.MoveNext
    Loop
    'FP = For Prodction, IQR = In Process Quality Rejected, PC = Production Complete
    'IQA = In Quality Check Approved, FQA = Final Quality Review Approved, FQR = Final Qaulity Review Rejected

    qryStr = "SELECT " & dataStr & " FROM " & prdTblName & " where FQR_User_Code='" & Application.UserName & _
    "' and line_status in('QP') order by line_status"
    Sheet3.Activate
    Sheet3.Columns.Clear
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="ODBC;DSN=mysql;" _
    , Destination:=Range("$A$2")).QueryTable
    .CommandText = qryStr
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    ' .SourceConnectionFile = _
    "C:\Users\Documents\My Data Sources\mydbconnection.odc"
    .ListObject.DisplayName = "Table_wds"
    '.Refresh BackgroundQuery:=False
    End With
    Range("C2").Select

    rs.MoveFirst
    i = 0
    Do While rs.EOF = False
    Dim rng As Range
    i = i + 1
    ActiveSheet.Cells(1, i) = rs("Comments")
    ActiveSheet.Cells(2, i) = rs("ActualColName")
    ActiveSheet.Cells(2, i).ID = rs("tblColName")
    rs.MoveNext
    Loop
    ActiveSheet.Cells(2, i + 1).ID = prdTblName
    rs.Close
    dbconn.Close

    MsgBox "Data downloaded sucessfully"
    Unload UserForm1

    End Sub

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: whenever i download data from my excelsheet it creates the duplicate rows and columns

    Welcome to the forum.

    We'd like to help you but first..

    Pls take some minutes to read forum rules and specially-in this case- rule#3 and add code tags around your code.
    Then anyone will be able to help you.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Move data on duplicate rows (different columns) to one row and delete extra rows?
    By Sagwa in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-01-2014, 08:25 AM
  2. Combining Duplicate Columns with the different data in their rows
    By cleefred in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 06-21-2013, 10:42 AM
  3. Duplicate rows, delete columns w/same data, combine columns w/unique data, Mac Excel 2011
    By msmcoin in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 2
    Last Post: 02-03-2013, 02:10 PM
  4. Delete duplicate rows with same data in certain columns for million rows
    By amlan009 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-08-2012, 12:53 AM
  5. Shift Rows to Columns in Excelsheet
    By bhimacg in forum Excel General
    Replies: 3
    Last Post: 08-21-2007, 10:55 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1