0

This code requests two input variables from the user and then uses those variables in a query to return data from internal company software. I adapted this code from a macro that someone else at the company uses without issue.

My macro:

  • Compiles without issue
  • Experiences run-time error '91': Object variable or With block variable not set
    • Occurs at With.Selection.ListObject.QueryTable

NOTE: Some details e.g. company name, server, login etc removed!

Option Explicit

Public EN As String
Public REV As String
Dim SearchString As String
Dim SearchString1 As String
Dim SearchString2 As String


Public Sub QueryImport()

EN = Import_Details.EN.Value
REV = Import_Details.REV.Value

Application.ScreenUpdating = False

Unload Import_Details

SearchString = EN
SearchString = EN & Chr(37)
SearchString = EN & Chr(47) & REV & Chr(37)

Sheets("Import").Range("A2").Select

With Selection.ListObject.QueryTable
     .Connection = _
    "ODBC;DRIVER=SQL Server;SERVER=XXXXXX-       XX;UID=XX;PWD=XXXXXX;APP=Microsoft Office 2013;WSID=XXXX;DATABASE=XXX-Live"
    .CommandText = _
    "SELECT Enquirys_list_with_Parts.""Item No"", Enquirys_list_with_Parts.Description, Enquirys_list_with_Parts.""Part No"", Enquirys_list_with_Parts.Qty, Enquirys_list_with_Parts.Price, Enquirys_list_with_Parts.Total" & Chr(13) & "" & Chr(10) & _
    "FROM ""Emax-Live"".dbo.ENQUIRY_LINES_LIST ENQUIRY_LINES_LIST, ""Emax-Live"".dbo.Enquirys_list_with_Parts Enquirys_list_with_Parts, ""Emax-Live"".dbo.Quote_Line_Qty_Drop_list Quote_Line_Qty_Drop_list" & Chr(13) & "" & Chr(10) & _
    "WHERE Enquirys_list_with_Parts.""Enquiry No"" = ENQUIRY_LINES_LIST.""Enquiry No"" AND Enquirys_list_with_Parts.""Part No"" = Quote_Line_Qty_Drop_list.Part_No AND ENQUIRY_LINES_LIST.Enquiry_Line_id = Enquirys_list_with_Parts.Enquiry_Line_id AND ((Enquirys_list_with_Parts.""Enquiry No"" Like '" & SearchString1 & "' And Enquirys_list_with_Parts.""Enquiry No"" Like '" & SearchString & "') AND (Quote_Line_Qty_Drop_list.""Quote No"" Like '" & SearchString2 & "'))" & Chr(13) & "" & Chr(10) & _
    "ORDER BY Enquirys_list_with_Parts.""Item No""" _

    .Refresh BackgroundQuery:=False
End With

Application.ScreenUpdating = True

End Sub
Community
  • 1
  • 1
Traxam
  • 1
  • 2
  • Avoid using Select and Selection http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros – puzzlepiece87 Aug 17 '16 at 16:03

1 Answers1

0

You are trying to reference an object that was never set.

Dim MyQueryTbl as QueryTable

Set MyQueryTbl = Sheet1.QueryTables(1)

Then you can manipulate your MyQueryTbl object.

SandPiper
  • 2,816
  • 5
  • 30
  • 52