Chaindrive export to Magento

chaindrive-export-to-magento.bas

Sub CreateMagentoImport()
'
' Create Magento Import from ChainDrive export
'
' @author   Derek Marcinyshyn <derek@marcinyshyn.com>
' @date     July 2, 2014
' @version  5

    Dim sourceSku As Long
        
    ' Duplicate Sheet1 and copy it over to Magento Import sheet
    Sheets("Sheet1").Copy after:=ActiveSheet
    ActiveSheet.Name = "Magento Import"
    
    ' Change the image paths to be all the same
    Dim imagePath As String
    imagePath = "w15/"
    
    Dim numberOfRowsImage As Long
    Dim rowNumberImage As Long
    numberOfRowsImage = Sheets("Magento Import").Cells(Rows.Count, 1).End(xlUp).Row

    For rowNumberImage = 2 To numberOfRowsImage
        Cells(rowNumberImage, 22).Value = imagePath & Cells(rowNumberImage, 4) & ".jpg"
        Cells(rowNumberImage, 23).Value = imagePath & Cells(rowNumberImage, 4) & ".jpg"
        Cells(rowNumberImage, 24).Value = imagePath & Cells(rowNumberImage, 4) & ".jpg"
    Next rowNumberImage
    
        
    ' Create new row if source_sku changes
    For sourceSku = Cells(Cells.Rows.Count, "D").End(xlUp).Row To 3 Step -1
        If Cells(sourceSku, "D") <> Cells(sourceSku - 1, "D") Then Rows(sourceSku).EntireRow.Insert
    Next sourceSku
    
    
    ' Get the next empty row and copy data from one line above
    Dim numberOfRows As Long
    Dim rowNumber As Long
    
    numberOfRows = Sheets("Magento Import").Cells(Rows.Count, 1).End(xlUp).Row + 1
    
    For rowNumber = 2 To numberOfRows
        
        ' A attribute_set
        If Cells(rowNumber, 1).Value = "" Then
            Cells(rowNumber, 1).Value = Cells(rowNumber - 1, 1).Value
        
        ' Wrap it around just checking the first cell of each row
        
           ' B type
            If Cells(rowNumber, 2).Value = "" Then
                Cells(rowNumber, 2).Value = "configurable"
            End If
            
            ' C categories -- TODO: make this interactive
            If Cells(rowNumber, 3).Value = "" Then
                Cells(rowNumber, 3).Value = Cells(rowNumber - 1, 3).Value
            End If
            
            ' D source_sku -- leave blank?
            
            ' E camber
            If Cells(rowNumber, 5).Value = "" Then
                Cells(rowNumber, 5).Value = Cells(rowNumber - 1, 5).Value
            End If
            
            ' F terrain
            If Cells(rowNumber, 6).Value = "" Then
                Cells(rowNumber, 6).Value = Cells(rowNumber - 1, 6).Value
            End If
            
            ' G board_width
            If Cells(rowNumber, 7).Value = "" Then
                Cells(rowNumber, 7).Value = Cells(rowNumber - 1, 7).Value
            End If
            
            ' H name -- remove last word
            If Cells(rowNumber, 8).Value = "" Then
                Cells(rowNumber, 8).Value = Left(Cells(rowNumber - 1, 8).Value, InStrRev(Cells(rowNumber - 1, 8).Value, " ") - 1)
            End If
            
            ' I sku -- copy source_sku
            If Cells(rowNumber, 9).Value = "" Then
                Cells(rowNumber, 9).Value = Cells(rowNumber - 1, 4).Value
            End If
            
            ' J snowboard_size -- leave blank?
            If Cells(rowNumber, 10).Value = "" Then
                Cells(rowNumber, 10).Value = ""
            End If
            
            ' K price
            If Cells(rowNumber, 11).Value = "" Then
                Cells(rowNumber, 11).Value = Cells(rowNumber - 1, 11).Value
            End If
            
            ' L qty -- blank
            
            ' M manufacturer -- leave blank? needs to be edited
            
            ' N weight
            If Cells(rowNumber, 14).Value = "" Then
                Cells(rowNumber, 14).Value = Cells(rowNumber - 1, 14).Value
            End If
            
            ' O length
            If Cells(rowNumber, 15).Value = "" Then
                Cells(rowNumber, 15).Value = Cells(rowNumber - 1, 15).Value
            End If
            
            ' P width
            If Cells(rowNumber, 16).Value = "" Then
                Cells(rowNumber, 16).Value = Cells(rowNumber - 1, 16).Value
            End If
            
            ' Q height
            If Cells(rowNumber, 17).Value = "" Then
                Cells(rowNumber, 17).Value = Cells(rowNumber - 1, 17).Value
            End If
            
            ' R simple_skus
            If Cells(rowNumber, 18).Value = "" Then
                ' check all rows above to see if they numerical if so then must be simple sku
                Dim simpleSku As String
                                
                Dim skuCounter As Integer
                skuCounter = 0
                
                Dim currentRowNumber As Integer
                currentRowNumber = rowNumber
                
                ' Loop through and append string
                While (IsNumeric(Cells(currentRowNumber - 1, 9)))
                    If (skuCounter = 0) Then simpleSku = Cells(currentRowNumber - 1, 9).Value
                    If (skuCounter > 0) Then simpleSku = simpleSku & ", " & Cells(currentRowNumber - 1, 9).Value
                    
                    skuCounter = skuCounter + 1
                    currentRowNumber = currentRowNumber - 1
                Wend
                
                Cells(rowNumber, 18).Value = CStr(simpleSku)
            End If
            
            ' S configurable_attributes
            If Cells(rowNumber, 19).Value = "" Then
                Cells(rowNumber, 19).Value = Cells(1, 10).Value
            End If
            
            ' T visibility
            If Cells(rowNumber, 20).Value = "" Then
                Cells(rowNumber, 20).Value = "Catalog, Search"
            End If
            
            ' U status
            If Cells(rowNumber, 21).Value = "" Then
                Cells(rowNumber, 21).Value = "Enabled"
            End If
            
            ' V image
            If Cells(rowNumber, 22).Value = "" Then
                Cells(rowNumber, 22).Value = "w15/" + Cells(rowNumber - 1, 4).Value + ".jpg"
            End If
            
            ' W small_image
            If Cells(rowNumber, 23).Value = "" Then
                Cells(rowNumber, 23).Value = "w15/" + Cells(rowNumber - 1, 4).Value + ".jpg"
            End If
            
            ' X thumbnail
            If Cells(rowNumber, 24).Value = "" Then
                Cells(rowNumber, 24).Value = "w15/" + Cells(rowNumber - 1, 4).Value + ".jpg"
            End If
            
            ' Y media_gallery -- skip?
            
            ' Z tax_class_id
            If Cells(rowNumber, 26).Value = "" Then
                Cells(rowNumber, 26).Value = Cells(rowNumber - 1, 26).Value
            End If
            
            ' AA is_in_stock
            If Cells(rowNumber, 27).Value = "" Then
                Cells(rowNumber, 27).Value = Cells(rowNumber - 1, 27).Value
            End If
            
            ' AB season
            If Cells(rowNumber, 28).Value = "" Then
                Cells(rowNumber, 28).Value = Cells(rowNumber - 1, 28).Value
            End If
            
            ' AC news_from_date
            If Cells(rowNumber, 29).Value = "" Then
                Cells(rowNumber, 29).Value = Cells(rowNumber - 1, 29).Value
            End If
            
            ' AD news_to_date
            If Cells(rowNumber, 30).Value = "" Then
                Cells(rowNumber, 30).Value = Cells(rowNumber - 1, 30).Value
            End If
        End If
        
    Next rowNumber
    
    ' Resize columns
    Dim numberOfColumns As Long
    numberOfColumns = Sheets("Magento Import").Cells(1, Columns.Count).End(xlToLeft).Column
    
    For columnNumber = 1 To numberOfColumns
        Columns(columnNumber).AutoFit
    Next columnNumber
    
        
End Sub