Как получить IP-адрес этого компьютера?
У вас есть несколько агрегированных данных внутри выражения case, и у вас нет группы по. Я делаю некоторые предположения здесь. Кроме того, вы должны привыкнуть использовать псевдонимы, это намного упрощает ситуацию. Я бы также предложил не ставить пробелы в именах столбцов, поскольку это заставляет вас постоянно заключать столбцы в квадратные скобки.
Нечто подобное должно сблизить вас.
SELECT p.[Project ID]
, MAX(case
when p.[City Name] is not null then concat(p.[City Name], ' ', p.[State])
when b.[Location] is not null then b.[Location]
when t.[Labor Location ID] is not null then l.[Labor Location Name]
else null
end) AS [Location]
FROM dbo.PS_Location l
RIGHT OUTER JOIN dbo.PS_Time t ON l.[Labor Location ID] = t.[Labor Location ID]
RIGHT OUTER JOIN dbo.PS_Proj p ON t.[Project ID] = p.[Project ID]
LEFT OUTER JOIN dbo.PS_Billing b ON p.[Project ID] = b.[Project ID]
GROUP BY p.[Project ID]
6 ответов
Вот адаптированный пример из Technet :
Function GetIPAddress()
Const strComputer As String = "." ' Computer name. Dot means local computer
Dim objWMIService, IPConfigSet, IPConfig, IPAddress, i
Dim strIPAddress As String
' Connect to the WMI service
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
' Get all TCP/IP-enabled network adapters
Set IPConfigSet = objWMIService.ExecQuery _
("Select * from Win32_NetworkAdapterConfiguration Where IPEnabled=TRUE")
' Get all IP addresses associated with these adapters
For Each IPConfig In IPConfigSet
IPAddress = IPConfig.IPAddress
If Not IsNull(IPAddress) Then
strIPAddress = strIPAddress & Join(IPAddress, ", ")
End If
Next
GetIPAddress = strIPAddress
End Function
Для этого требуется, чтобы у вас была библиотека сценариев Microsoft WMI в ссылках на проект.
Существует еще один простой способ использования ipconfig. http://www.vbaexpress.com/kb/getarticle.php?kb_id=537
У Codeproject есть хорошая статья о том, как это сделать с помощью .net: http://www.codeproject.com/KB/cs/network.aspx
Вы всегда можете создать из этого исполняемый файл консоли и вызывать его из VBA.
RO
nbtstat -n might do the job on XP anyway. Not sure about other Windows versions or about localisation in other languages. Partial sample output:
C:\Documents and Settings\colin>nbtstat -n
Local Area Connection: Node IpAddress: [192.168.1.100] Scope Id: []
NetBIOS Local Name Table
etc.
Вы могли бы выполнить команду оболочки ipconfig и проанализировать возвращенные результаты?
Я нашел пару примеров: -
http://www.everythingaccess.com/tutorials.asp?ID=Get-all-IP-Addresses-of-your-machine
http://puremis.net/excel/code/079.shtml
ИЗМЕНИТЬ
Вот код из первой ссылки с небольшими изменениями
Option Explicit
' VBA MODULE: Get all IP Addresses of your machine
' (c) 2005 Wayne Phillips (http://www.everythingaccess.com)
' Written 18/05/2005
'
' REQUIREMENTS: Windows 98 or above, Access 97 and above
'
' Please read the full tutorial here:
' http://www.everythingaccess.com/tutorials.asp?ID=Get-all-IP-Addresses-of-your-machine
'
' Please leave the copyright notices in place.
' Thank you.
'
'Option Compare Database
'A couple of API functions we need in order to query the IP addresses in this machine
Public Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)
Public Declare Function GetIpAddrTable Lib "Iphlpapi" (pIPAdrTable As Byte, pdwSize As Long, ByVal Sort As Long) As Long
'The structures returned by the API call GetIpAddrTable...
Type IPINFO
dwAddr As Long ' IP address
dwIndex As Long ' interface index
dwMask As Long ' subnet mask
dwBCastAddr As Long ' broadcast address
dwReasmSize As Long ' assembly size
Reserved1 As Integer
Reserved2 As Integer
End Type
Public Function ConvertIPAddressToString(longAddr As Long) As String
Dim IPBytes(3) As Byte
Dim lngCount As Long
'Converts a long IP Address to a string formatted 255.255.255.255
'Note: Could use inet_ntoa instead
CopyMemory IPBytes(0), longAddr, 4 ' IP Address is stored in four bytes (255.255.255.255)
'Convert the 4 byte values to a formatted string
While lngCount < 4
ConvertIPAddressToString = ConvertIPAddressToString + _
CStr(IPBytes(lngCount)) + _
IIf(lngCount < 3, ".", "")
lngCount = lngCount + 1
Wend
End Function
Public Function GetFirstNonLocalIPAddress()
Dim Ret As Long, Tel As Long
Dim bytBuffer() As Byte
Dim IPTableRow As IPINFO
Dim lngCount As Long
Dim lngBufferRequired As Long
Dim lngStructSize As Long
Dim lngNumIPAddresses As Long
Dim strIPAddress As String
On Error GoTo ErrorHandler:
Call GetIpAddrTable(ByVal 0&, lngBufferRequired, 1)
If lngBufferRequired > 0 Then
ReDim bytBuffer(0 To lngBufferRequired - 1) As Byte
If GetIpAddrTable(bytBuffer(0), lngBufferRequired, 1) = 0 Then
'We've successfully obtained the IP Address details...
'How big is each structure row?...
lngStructSize = LenB(IPTableRow)
'First 4 bytes is a long indicating the number of entries in the table
CopyMemory lngNumIPAddresses, bytBuffer(0), 4
While lngCount < lngNumIPAddresses
'bytBuffer contains the IPINFO structures (after initial 4 byte long)
CopyMemory IPTableRow, _
bytBuffer(4 + (lngCount * lngStructSize)), _
lngStructSize
strIPAddress = ConvertIPAddressToString(IPTableRow.dwAddr)
If Not ((strIPAddress = "127.0.0.1")) Then
GetFirstNonLocalIPAddress = strIPAddress
Exit Function
End If
lngCount = lngCount + 1
Wend
End If
End If
Exit Function
ErrorHandler:
MsgBox "An error has occured in GetIPAddresses():" & vbCrLf & vbCrLf & _
Err.Description & " (" & CStr(Err.Number) & ")"
End Function