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

 

[Fluff]

Try

sHostName = Environ("userprofile")
'=======================================================
fPath = sHostName & "\Desktop\Image Name Processing\"

 

[SteveOranjin]

Try

sHostName = Environ("userprofile")
'=======================================================
fPath = sHostName & "\Desktop\Image Name Processing\"

Same result.

 

[Fluff]

If you add the msgbox what does it say?

sHostName = Environ("userprofile")
'=======================================================
fPath = sHostName & "\Desktop\Image Name Processing\"
MsgBox fPath

 

[SteveOranjin]

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\"
Msgbox fPath
'' This would normally be entered in as, "C:\Users\Data Coordinator I\Desktop\Image Name Processing\"

That returns "Data-COORDINATO\Desktop\Image Name Processing\

Steve

 

[Fluff]

What does the msgbox say with the code I suggested?

 

[SteveOranjin]

It says "DATA-COORDINATO\Desktop\Image Name Processing"

When I enter the file path manually, I input the below, and it works.

"C:\Users\Data Coordinator I\Desktop\Image Name Processing"

 

[SteveOranjin]

MrExcel is not letting me add the file path correctly. There is an additional backslash after the last g there.

 

[Fluff]

Not sure what is happening, as when I run it I get
C:\Users\Fluff\Desktop\Image Name Processing\

 

[Fluff]

What if you try

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