Compile Error: Duplicate declaration in current scope

Question

I'm trying to gather data from multiple sheets in a selected workbook. I'm using the following code:

Sub Multiplesheet()

Dim filepath As Variant
Dim outputFilePath As String
Dim outputSheetName As String
Dim sql As String
Dim wbk As Workbook, wks As Worksheet
Dim rng As Excel.Range
Dim sheetname As Variant

'To which file and sheet within the file should the output go?
outputFilePath = "C:\Users\z003k50s\Desktop\Test\Output.xlsx"
outputSheetName = "Sheet1"

For Each filepath In Application.GetOpenFilename(FileFilter:="Excel Files (*.xl*), *.xl*", MultiSelect:=True)
    Set schema = conn.OpenSchema(adSchemaTables)
    For Each sheetname In schema.GetRows(, , "TABLE_NAME") 'returns a 2D array of one column
        sql = sql & _
            "UNION ALL SELECT F1 " & _
            "FROM [" & sheetname & "]" & _
                "IN """ & filepath & """ ""Excel 12.0;"""
    Next
Next
sql = Mid(sql, 5) 'Gets rid of the UNION ALL from the first SQL

Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
 With conn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=""" & filepath & """;" & _
        "Extended Properties=""Excel 12.0;HDR=No"""
    .Open
    Set rs = .Execute(sql)
    Set wbk = Workbooks.Open(outputFilePath, , True)
    Set wks = wbk.Sheets(outputSheetName)
    wks.Cells(2, 1).CopyFromRecordset rs
    wks.Columns.AutoFill
    .Close
End With

End Sub

When I debug it highlights:

 conn As New ADODB.Connection

I'm very new to Excel VBA and I don't know what it means.

Exactly what it says - you have duplicated a declaration. In your code, `conn` has already been used before you `Dim` it, so either there is already a public variable called `conn`, `conn` is being used as a variant in this code before you `Dim` it which would cause a compile error, or you haven't shown us the full code. — SierraOscar, Sep 15 '15 at 12:11
Put `Option Explicit` at the top of hte module sheet in the Declarations section. Also advise you to go into Tools ► Options and put a checkmark beside **Require Variable Declaration** to avoid these problems in the future. — , Sep 15 '15 at 12:14
I've sent you guys, the full code, and I can't see where I have duplicated a declaration. — Philip.T, Sep 15 '15 at 12:15
If you are not required to declare your variables before use then they are created -on-the-fly as **conn** is with `Set schema = conn.OpenSchema(adSchemaTables)`. You then try and declare it several lines later as `Dim conn As New ADODB.Connection` but it has already been 'declared' through its use. — , Sep 15 '15 at 12:17

SierraOscar · Accepted Answer · 2015-09-15T13:01:24.963

0

Snippet of your code:

For Each filepath In Application.GetOpenFilename(FileFilter:="Excel Files (*.xl*), *.xl*", MultiSelect:=True)
'----------
    Set schema = conn.OpenSchema(adSchemaTables) '<~~~ HERE YOU HAVE USED 'conn'
'----------

    For Each sheetname In schema.GetRows(, , "TABLE_NAME") 'returns a 2D array of one column
        sql = sql & _
            "UNION ALL SELECT F1 " & _
            "FROM [" & sheetname & "]" & _
                "IN """ & filepath & """ ""Excel 12.0;"""
    Next
Next
sql = Mid(sql, 5) 'Gets rid of the UNION ALL from the first SQL

'---------
Dim conn As New ADODB.Connection '<~~~ already exists, so duplicate declaration
'---------

Dim rs As ADODB.Recordset
 With conn

As you can see from my comments, you have used conn and then tried to dimension (Dim) it, which is why you are getting the compile error.

edited Sep 15 '15 at 13:01

answered Sep 15 '15 at 12:40

SierraOscar

17,507
6
40
68

Thanks, now I fixed that problem, but now I get another error when I run the macro. When I try to run it, it comes with the compile error: Argument not optional and when I debug it highlights `AutoFill` in the line `wks.Columns.AutoFill` – Philip.T Sep 16 '15 at 08:06
The `AutoFill` method requires a destination - otherwise how will it know where to fill to? Something like `wks.Columns.AutoFill Range("A1:D10")` Here's the MSDN article for it: https://msdn.microsoft.com/en-us/library/office/ff195345.aspx – SierraOscar Sep 16 '15 at 08:15
God dammit, I get errors after errors, I've been working on this for like a week now and I keep getting errors. I want to copy data from multiple worksheets in a selected workbook, but I just can't get it to work, one guy here on Stackoverflow have already tried to help me, but I kept getting errors. – Philip.T Sep 16 '15 at 08:23
I'd make that a separate question, as it's outside the scope of this one but you basically just need to search "Loop through all pages in workbook" and you should find what you're looking for. – SierraOscar Sep 16 '15 at 08:31
I've made a separate question like a week ago or more and one guy tried to help me, unfortunately without luck. http://stackoverflow.com/questions/32396339/get-data-from-multiple-sheets-in-a-selected-workbook – Philip.T Sep 16 '15 at 08:46

Compile Error: Duplicate declaration in current scope

1 Answers1