CODEWITHSUNDEEP
vb.netexcel
James
James

Reputation: 11

'Range' is an interface type and cannot be used as an expression

I have been trying everything and I have absolutely no idea why this is not working. I have looked at countless previous questions and answers and nothing works. I have a dialog where the user selects a number of files that can then be imported into Excel. The problem arises when setting the import range.

Dim files As New OpenFileDialog
files.Multiselect = True
files.InitialDirectory = My.Settings.path

If (files.ShowDialog() = DialogResult.OK) Then
    My.Settings.files = files.InitialDirectory
    My.Settings.Save()
    MsgBox("Files to be imported: " & files.Multiselect, MessageBoxIcon.Information)
    If String.IsNullOrEmpty(My.Settings.path) Then
        MsgBox("Warning! No files were imported!", MessageBoxIcon.Exclamation)
    End If
End If  

Dim openExcel As New Microsoft.Office.Interop.Excel.Application
Dim openWorkbook As New Microsoft.Office.Interop.Excel.Workbook
Dim openWorksheet As New Microsoft.Office.Interop.Excel.Worksheet

openExcel = CreateObject("Excel.Application")
openExcel.Visible = True
openExcel.UserControl = True
openWorkbook = openExcel.Workbooks.Add
openWorksheet = openWorkbook.ActiveSheet

With openWorksheet.QueryTables.Add(Connection:=My.Settings.files, Destination:=Range("$A$1"))

End With

It keeps giving me the error: "BC30111 'Range' is an interface type and cannot be used as an expression"

Upvotes: 1

Views: 1672

Answers (3)

RBarryYoung
RBarryYoung

Reputation: 56755

Your Range is returning a different worksheet than the QueryTable is on.

Try it like this:

With openWorksheet.QueryTables.Add(Connection:=My.Settings.files, Destination:=openWorksheet.Range("$A$1"))

Or this:

With openWorksheet
    With .QueryTables.Add(Connection:=My.Settings.files, Destination:=.Range("$A$1"))

Upvotes: 1

PKatona
PKatona

Reputation: 639

I think the issue is that it doesn't know where "Range" is coming from, try this:

With openWorksheet
    QueryTables.Add(Connection:=My.Settings.files, Destination:=Range("$A$1"))
End

Upvotes: 0

Beutler
Beutler

Reputation: 83

See https://msdn.microsoft.com/en-us/library/office/ff837764.aspx

Try removing the $ from Range("$A$1")

Upvotes: 0

Related Questions