Look for the error before the segment highlighted in blue. Add Key:=Range("C2: C7"), _ Sort On:=xl Sort On Values, Order:=xl Ascending, Data Option:=xl Sort Normal With Active Workbook. You can also extract data from the Internet, from text files and from other Excel or CSV files.
We can deduce that VBA is talking about the missing quotation mark. Basically you need a connection (var Conn in the macro below) and an SQL sentence (var SQL in the macro below) to automate the extraction of data for reporting purposes.
Here is a tip and an exercise that will give you an idea of what you will find in the complete chapter 11 of the Downloadable Tutorial on Excel Macros. A variable is an object that you create and in which you can store text, dates, numbers or almost anything else. The first good reason is to make your code dynamic, to avoid hard coding some values.
You get the following message box telling you that you are missing a "list separator". SQL stands for Structured Query Language and is the language used to extract data from almost all databases like Access and SQL Server from Microsoft or, Oracle, Sybase, SAP and also most accounting applications.
DLL" _ (By Val hkey As Long) As Long Public Const HKEY_CURRENT_USER = &H80000001 ' Show the value of an Excel 7 entry Sub Test Show Excel Text() Msg Box Get Registry Value(HKEY_CURRENT_USER, _ "software\microsoft\excel\7.0\microsoft excel", "Default Path") End Sub 'Pass: ' (1) the KEY (e.g., HKEY_CLASSES_ROOT), ' (2) the SUBKEY (e.g., "Excel.
Sheet.5"), ' (3) the value's name (e.g., "" [for default] or "whatever") Function Get Registry Value(KEY As Long, Sub Key As String, _ Value Name As String) As String Dim Buffer As String * MAX_STRING, Return Code As Long Dim Key Hdl Addr As Long, Value Type As Long, Value Len As Long Dim Temp Buffer As String, Counter As Integer Value Len = MAX_STRING Return Code = Reg Open Key A(KEY, Sub Key, Key Hdl Addr) If Return Code = 0 Then Return Code = Reg Query Value Ex A(Key Hdl Addr, Value Name, _ 0&, Value Type, Buffer, Value Len) Reg Close Key Key Hdl Addr 'If successful Value Type contains data type ' of value and Value Len its length If Return Code = 0 Then Select Case Value Type Case REG_BINARY For Counter = 1 To Value Len Temp Buffer = Temp Buffer & _ Stretch(Hex(Asc(Mid(Buffer, Counter, 1)))) & " " Next Get Registry Value = Temp Buffer Case REG_DWORD Temp Buffer = "0x" For Counter = 4 To 1 Step -1 Temp Buffer = Temp Buffer & _ Stretch(Hex(Asc(Mid(Buffer, Counter, 1)))) Next Get Registry Value = Temp Buffer Case Else Get Registry Value = Buffer End Select Exit Function End If End If Get Registry Value = "Error" End Function Function Stretch(Byte Str As String) As String If Len(Byte Str) = 1 Then Byte Str = "0" & Byte Str Stretch = Byte Str End Function Option Explicit Option Base 1 Private Declare Function WNet Get Connection _ Lib "User" (By Val Local Name As String, _ By Val Remote Name As String, _ Ret Length As Integer) As Integer ' 32 Bit version of above Private Declare Function WNet Get Connection A _ Lib "MPR.
" End Sub Function Is Colour Printer() As Boolean ' *** Alan Warriner 1998 *** '[email protected]'wrapper for Get Printer Colours function 'returns TRUE if a colour printer 'returns FALSE if not colour or an error occurred Is Colour Printer = Get Printer Colours 2 End Function Function Get Printer Colours() As Integer ' *** Alan Warriner 1998 *** '[email protected]'obtain the number of colours active printer is capable of printing '2 colours (or less?