Replies

The first part puts up a message but does not exit the routine, so the duplicate check is then done again anyway. It also monitors columns A:G even though many of them are irrelevant to the duplicate check.

I think it would be useful to have – in words, not code – an explanation of exactly what should happen.

Here’s a simple example, based on your workbook layout that colours each bar to the same as each cell’s fill colour:

Code:

    Dim dataRange As Range
    Dim n As Long
    Set dataRange = Range("A2:A5")
    
    For n = 1 To dataRange.Cells.Count
        ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Points(n).Format.Fill.ForeColor.RGB = _
                                                dataRange.Cells(n).Interior.Color
    Next n

It looks to me as though the first two sections are essentially doing the same thing – i.e. checking for duplicates – so there isn’t much point in doing it twice.

If you wish for each sales person to have a different color then the graph needs to be set up where each sales person is a series.

You can have different colours for individual points in a bar chart, so you don’t have to use separate series.

I must be missing something here as I don’t actually see the problem. If you simply change the formatting rules that currently make the cell green so that they don’t apply a fill but change the font instead, you don’t have to worry about the cell fill.

What data source are you extracting from?

Check the location specified in the Save section of Options. It appears to be invalid.

You need a couple of additional coercions. First you need N() – or something equivalent – to force an array of values to be returned. Second you need to coerce the True/False to 1/0. So something like this should work:

=SUM(–(0N(INDIRECT(“A”&(31+25*(ROW(1:15)-1))))))

If the cells are either blank or have a number, using COUNT would be simpler (unless they can contain 0 and you don’t want to count those).

Otherwise, you could use OFFSET:

=SUMPRODUCT(–(0N(OFFSET(A30,25*(ROW(1:15)-1)+1,0))))

Same problem. An ADODB.Field doesn’t have a Size property. You need to declare it as DAO.Field

If you add an additional INDEX to Maudibe’s approach, this doesn’t require array-entry:

=INDEX(B3:B7,MATCH(1,INDEX(N(B3:B7+0>3),),0))

I’d say your problem is that you haven’t specified the Recordset type and your ADO reference precedes your DAO one. An ADO recordset doesn’t have an Edit method. Declare your rst variable as DAO.Recordset

You could perhaps use this version of your original:

Code:

Sub ExportCSVFINAL()

    Dim wbkExport             As Workbook
    Dim shtToExport           As Worksheet


    Worksheets("TextFile").Activate
    fname = Cells(1, 15).Value


    Set shtToExport = ThisWorkbook.Worksheets("MarrReformatted")     'Sheet to export as CSV
    Set wbkExport = Application.Workbooks.Add
    shtToExport.Copy Before:=wbkExport.Worksheets(wbkExport.Worksheets.Count)
    'check if file exists
    If Dir(fname)  vbNullString Then
        ' prompt to overwrite
        If MsgBox(Prompt:="File already exists. Do you want to overwrite?", _
                    Buttons:=vbYesNo + vbQuestion, Title:="Overwrite file?") = vbNo Then
            ' exit if no
            Exit Sub
        Else
            ' delete the file if yes
            Kill fname
        End If
    End If
    wbkExport.SaveAs Filename:=fname, FileFormat:=xlCSV

    wbkExport.Close SaveChanges:=True

End Sub

I would guess it’s some code that is running automatically, either in an add-in or in your personal macro workbook. If you hold Ctrl down while opening Excel and answer Yes to open it in safe mode, then close it down, do you still see the process left running?

If you want to add macro buttons to a toolbar, you can either add them to the Ribbon or to the Quick Access Toolbar (QAT) that sits either just above or just below the ribbon. You can customise both through the Excel Options dialog (File – Options)