Variable doesn't return the file path with var = Dir(filepath)

[Tony Williams]

I am trying to save a file to 3 different folders on two different computers.
The file path depends on which computer I am doing the save operation.
I am trying to loop on a file path variable and the variable doesn't return the file path.
Also, the ActiveWorkbook.SaveAs function doesn't work either.

Sub SaveToLocations()


'Store the opened file name:
Dim OrigName As String
Dim i As Integer
Dim currentFolder As String
Dim compName As String
Dim folderHP1 As String
Dim folderHP2 As String
Dim folderHP3 As String
Dim folderTP1 As String
Dim folderTP2 As String
Dim folderTP3 As String
OrigName = ActiveWorkbook.FullName
i = 1
currentFolder = " "
compName = " "
folderHP1 = "My Passport"
folderHP2 = "\\Thinkpad X220\Users\skyzzx"
folderHP3 = "C:\Users\User\Documents"
folderTP1 = Dir("\\HP\Users\User\Documents")
folderTP2 = "\\HP\My Passport"
folderTP3 = "C:\Users\skyzz\Documents"

' Turn off screen updating:
Application.DisplayAlerts = False


If FileFolderExists("C:\Users\User\Documents") Then
compName = "HP"
End If


If FileFolderExists("C:\Users\skyzz\Documents") Then
compName = "TP"
End If


Do
currentFolder = "folder" & compName & i
If FileFolderExists(Dir(currentFolder)) Then
ActiveWorkbook.SaveAs Dir("folder" & compName & i) & ActiveWorkbook.Name
MsgBox "My file saved."
Else
MsgBox "My file does not exist!"
End If
i = i + 1
Loop Until i = 4


' Turn on screen updating:
Application.DisplayAlerts = True


End Sub


Public Function FileFolderExists(strFullPath As String) As Boolean
'Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: Check if a file or folder exists
On Error GoTo EarlyExit
If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True

EarlyExit:
On Error GoTo 0
End Function

 

[mole999]

ENVIRON might help some of your task

This code will list the available in to the immediate window

<code>Sub ListEnvironmentVariables()
'each environment variable in turn
Dim EnvironmentVariable As String
'the number of each environment variable
Dim EnvironmentVariableIndex As Integer
Dim i As Long
'get first environment variables
EnvironmentVariableIndex = 1
EnvironmentVariable = Environ(EnvironmentVariableIndex)
'loop over all environment variables till there are no more
Do Until EnvironmentVariable = ""
'get next e.v. and print out its value
With Worksheets("temp")
            .Cells(i, 1).Value = EnvironmentVariableIndex
            .Cells(i, 2).Value = EnvironmentVariable

        End With
'go on to next one
EnvironmentVariableIndex = EnvironmentVariableIndex + 1
i = i + 1
EnvironmentVariable = Environ(EnvironmentVariableIndex)
Loop
End Sub</code>

User is a common place in windows so you could use the specific enviorn to point to the same location regardless of user name

 

[Tony Williams]

mole999, thank you for the reply.
I am having a difficult time understanding where I can pull an environment variable and use it to point to these locations.
I am sorry if my code isn't very clear. I am trying to save a file into three different locations on two different computers.
This is a sample file that I am attempting to understand before I start with a larger dataset.
The difficulty that I am having issues with is using a variable to point to a variable that holds a file location. I can't seem to figure out how to drill down to the second variables data.
I tried the Dir() function in the code above with no luck.

 

[Tony Williams]

I am sorry. It is late and I forgot to do the following:
Windows 10 Pro
Office 365 Business

Sub SaveToLocations()


'Store the opened file name:
Dim OrigName As String
Dim i As Integer
Dim currentFolder As String
Dim compName As String
Dim folderHP1 As String
Dim folderHP2 As String
Dim folderHP3 As String
Dim folderTP1 As String
Dim folderTP2 As String
Dim folderTP3 As String
OrigName = ActiveWorkbook.FullName
i = 1
currentFolder = " "
compName = " "
folderHP1 = "My Passport"
folderHP2 = "\\Thinkpad X220\Users\skyzzx"
folderHP3 = "C:\Users\User\Documents"
folderTP1 = Dir("\\HP\Users\User\Documents")
folderTP2 = "\\HP\My Passport"
folderTP3 = "C:\Users\skyzz\Documents"

' Turn off screen updating:
Application.DisplayAlerts = False


If FileFolderExists("C:\Users\User\Documents") Then
compName = "HP"
End If


If FileFolderExists("C:\Users\skyzz\Documents") Then
compName = "TP"
End If


Do
currentFolder = "folder" & compName & i
If FileFolderExists(Dir(currentFolder)) Then
ActiveWorkbook.SaveAs Dir("folder" & compName & i) & ActiveWorkbook.Name
MsgBox "My file saved."
Else
MsgBox "My file does not exist!"
End If
i = i + 1
Loop Until i = 4


' Turn on screen updating:
Application.DisplayAlerts = True


End Sub


Public Function FileFolderExists(strFullPath As String) As Boolean
'Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: Check if a file or folder exists
On Error GoTo EarlyExit
If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True

EarlyExit:
On Error GoTo 0
End Function

 

[Fluff]

How about

Sub SaveToLocations()


'Store the opened file name:
   Dim OrigName As String
   Dim i As Long, j As Long
   Dim FolderAry As Variant
   OrigName = ActiveWorkbook.FullName
   
   FolderAry = Array("My Passport", "\\Thinkpad X220\Users\skyzzx", "C:\Users\user\Documents", "\\HP\Users\User\Documents", "\\HP\My Passport", "C:\Users\skyzz\Documents")
   
   ' Turn off screen updating:
   Application.DisplayAlerts = False
   
   
   If LCase(Environ("username")) = "user" Then
      i = 1
   ElseIf LCase(Environ("username")) = "skyzz" Then
      i = 4
   Else
      MsgBox "Unknown username"
      Exit Sub
   End If
   
   
   For j = 1 To 3
      If Not Dir(FolderAry(i), vbDirectory) = vbNullString Then
         ActiveWorkbook.SaveAs FolderAry(i) & "\" & ActiveWorkbook.Name
         MsgBox "My file saved."
      Else
         MsgBox "My file does not exist!"
      End If
      i = i + 1
   Next j
   
   
   ' Turn on screen updating:
   Application.DisplayAlerts = True


End Sub

 

[Tony Williams]

Fluff, thank you for your response.
I changed i = 1 and i = 4 to i = 0 and i = 3, since arrays start at 0.
After this change your code worded perfectly.
Again, thank you very much.