How to save workbook in different folders depending on country code

hanz753

Board Regular
Joined
Aug 9, 2017
Messages
53
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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
well it would be along the lines of path = "C:\somepath" & CountryID & "" where CountryID is a string containing a country code.
 
Upvote 0
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"
 
Upvote 0
Hello,

I could change the file names to say:

E.g. Country Code = GB
Path = Path = "C:\ Documents \ Countries \ UK \GB"
 
Upvote 0
you could load an array with the country codes in one part and the paths in another
 
Upvote 0
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
 
Upvote 0
yes something like
Code:
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
 
Upvote 0
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
 
Upvote 0
another way using arrays so you can increase the countries

Code:
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
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top