VBA: Capture Computer Name and Insert it into String.

[SteveOranjin]

Hello,

I'd like to be able to send this file to other people and have it work for them, without having to go through some of the VBA editing that I must go through presently.

The macro works fine, but when it goes all the way to the file destination, it says that nothing is present. What this indicates to me is that file name path is not going through as I'd like.

The below code, highlighted in red, is what I was made to understand would work for getting VBA to grab the name of the computer and return it. I added a comment which is not really in the code on my computer, which I have underlined. That code is also in red. The comment should grant additional insight.

Hope you are well,

Steve

Sub Image_Import2()Dim fileList() As String
Dim fName As String
Dim fPath As String
Dim i As Integer
Dim startrow As Integer
Dim ws As Worksheet
Dim filetype  As String
Dim sHostName As String


' Dim shellApp As Shell32.Shell


'=======================================================
' Set shellApp = New Shell32.Shell
' fName = shellApp.BrowseForFolder(0, "Please choose a folder", 0).Self.Path
' (last arg. 512 instead of 0 to disable new folder)
' MsgBox fPath
'=======================================================




sHostName = Environ$("computername")
'=======================================================
fPath = "C:\Users\" & sHostName & "\Desktop\Image Name Processing\"
'' This would normally be entered in as, "C:\Users\Data Coordinator I\Desktop\Image Name Processing\"

' fPath = "C:\Image Directory\Mfr Images\"
' fPath = "C:\Image Directory\SizeNSort\Input\"
' fPath = "C:\Users\gweha\Documents\Bravo_MLS\MLS_VENDOR_FILES\Artcraft Lighting\Images\"
' fPath = "\\NAS\Volume_1\Vendor Files\Checkolite\2011 Images\"
' fPath = "\\NAS\Volume_1\Vendor Files\Cyan\Cyan Images\"


filetype = "*"
Set ws = Worksheets("Import Images")
startrow = 1    'starting row for the data
'========================================================


fName = Dir(fPath & "*." & filetype)
While fName <> ""
    i = i + 1
    ReDim Preserve fileList(1 To i)
    fileList(i) = fName
    fName = Dir()
Wend
If i = 0 Then
    MsgBox "No files found"
    Exit Sub
End If
    
For i = 1 To UBound(fileList)
    ws.Range("B" & i + startrow).Value = fileList(i)
Next
Columns(1).AutoFit


End Sub

 

[SteveOranjin]

C:\Users\DATA-COORDINATO\Desktop\Image Name Processing\

 

[SteveOranjin]

I don't know what it would grab, "DATA-COORDINATO" instead of "Data Coordinator I" is the name of the computer.

 

[Fluff]

"DATA-COORDINATO" is the Windows username, which normally dictates the path.
If you need the name of the machine try

sHostName = Environ("computername")
'=======================================================
fPath = "C:\Users\" & sHostName & "\Desktop\Image Name Processing\"
MsgBox fPath

 

[SteveOranjin]

That's the same thing as I provided initially (minus the message box)

I entered it again, just to check, and I got the same result.

 

[Fluff]

It seems odd that the file path is not the same as the username, but without access to your computer, I'm afraid I can't help any further.

 

[Scott Huish]

It's not completely clear from your post whether you want the computer name or the username. Disregarding the rest of your path for the moment. Do either of these Msgboxes return the result you want?

Sub GetUserName_Environ()
    Dim ObjWshNw As Object
    Set ObjWshNw = CreateObject("WScript.Network")
    
    MsgBox ObjWshNw.UserName
    MsgBox ObjWshNw.ComputerName
End Sub

 

[SteveOranjin]

"MsgBox ObjWshNew.UserName" returns "Data Coordinator I" which is what I was looking for.

 

[SteveOranjin]

It's not completely clear from your post whether you want the computer name or the username. Disregarding the rest of your path for the moment. Do either of these Msgboxes return the result you want?

Sub GetUserName_Environ()
    Dim ObjWshNw As Object
    Set ObjWshNw = CreateObject("WScript.Network")
    
    MsgBox ObjWshNw.UserName
    MsgBox ObjWshNw.ComputerName
End Sub

What worked. The Macro is now working. Thank you.

Fluff, as always, thank you very much.