How to save workbook in different folders depending on country code

[hanz753]

Hello,

I have the below code, I have nearly completed what I am trying to do. The final stage would be to save each workbook into different folders depending on the country code. Can you please help?




Private Sub filename_cellvalue()


Dim Path As String
Dim filename As String
Dim filename2 As String
'Dim country As String


Path = ""
filename = Range("A1")
filename2 = Range("B1")


ActiveWorkbook.SaveAs filename:=Path & filename & filename2 & ".xls", FileFormat:=xlNormal
End Sub

 

[nemmi69]

well it would be along the lines of path = "C:\somepath" & CountryID & "" where CountryID is a string containing a country code.

 

[hanz753]

No, the country code won't be included in the path.

E.g. Country code = GB
Path = "C:\ Documents \ Countries \ UK \ Hanz UK"

Country code = DE
Path = "C:\ Documents \ Countries \ Germany \ Hanz Germany"

 

[hanz753]

Hello,

I could change the file names to say:

E.g. Country Code = GB
Path = Path = "C:\ Documents \ Countries \ UK \GB"

 

[nemmi69]

you could load an array with the country codes in one part and the paths in another

 

[hanz753]

I am not sure how to do that, I was wondering if I could include an IF function into the code.

If country = "DE" Then
Path = "C:\ Documents \ Countries \ UK \ GB"
ActiveWorkbook.SaveAs filename:=Path & filename & filename2 & ".xls", FileFormat:=xlNormal

 

[nemmi69]

yes something like

If [COLOR=#333333]Country Code = GB Then[/COLOR]
[COLOR=#333333]    Path = [/COLOR][COLOR=#333333][COLOR=#333333]Path = "C:\ Documents \ Countries \ UK \GB"[/COLOR][/COLOR]
ElseIf [COLOR=#333333]Country Code = DE Then[/COLOR]
[COLOR=#333333]    Path = "C:\ Documents \ Countries \ Germany \ Hanz Germany"
Else
    Path ="NOPATH"
End If
[/COLOR]

you can have multiple elseif's

 

[hanz753]

This is the code I am using it doesn't seem to be saving in the correct folder. It saves in the file in Documents.

Private Sub filename_cellvalue()


Dim Path As String
Dim filename As String
Dim filename2 As String
Dim country As String




filename = Range("A1")
filename2 = Range("B1")
country = Range("C1")


If country = GB Then
Path = Path = "C:\ Documents \ Countries \ UK \Hanz UK"
ElseIf country = DE Then
Path = Path = "C:\ Documents \ Countries \ Germany \ Hanz Germany"
ElseIf country = FS Then
Path = Path = "C:\ Documents \ Countries \ Germany \ Hanz France"
ElseIf country = NO Then
Path = Path = "C:\ Documents \ Countries \ Germany \ Hanz Norway"
ElseIf country = SE Then
Path = Path = "C:\ Documents \ Countries \ Germany \ Hanz Sweden"
End If




ActiveWorkbook.SaveAs filename:=Path & filename & filename2 & ".xls", FileFormat:=xlNormal




End Sub

 

[nemmi69]

another way using arrays so you can increase the countries

Dim CountryID As Variant
Dim PathRef As Variant
Dim LBcid As Long
Dim UBcid As Long
Dim LBpr As Long
Dim UBpr As Long


CountryID = Array("GB", "D", "S")
PathRef = Array("C:\Documents\Countries\UK \GB\", "C:\Documents\Countries \Germany \Hanz Germany\", "C:\Documents\Countries\Spain\Hanz Spain\")


LBcid = LBound(CountryID)
UBcid = UBound(CountryID)
LBpr = LBound(PathRef)
UBpr = UBound(PathRef)


If UBcid = UBpr Then
   Range("A1").Value = CountryID(0)
   Range("A2").Value = CountryID(1)
   Range("A3").Value = CountryID(2)
   Range("B1").Value = PathRef(0)
   Range("B2").Value = PathRef(1)
   Range("B3").Value = PathRef(2)
    
End If

 

[nemmi69]

Note on above :- I think there could be an issue with the length of the strings. This could be rectified by splitting it up in the fixed part of the pat and variable.

IE
dim FixPath as string

FixedPath = "C:\Documents\Countries"

then you could shorten PathRef
PathRef = Array("UK \GB", "Germany \Hanz Germany", "Spain\Hanz Spain")

Then rebuild the Path as follows

Path = FixedPath & PathRef(x)