PDA

View Full Version : VBA code to identify folder based on folder name given in a excel sheet



rockybalboa
04-29-2014, 01:27 AM
Hi,

Can anybody help me with this?

Suppose the name (ABCD) is mentioned in range A2. There is a folder named ABCD in C:\Users\ . I want the macro to identify ABCD folder based on range A2 and save the excel sheet in that folder.

Thanks

Bob Phillips
04-29-2014, 01:51 AM
Is not just



ActiveWorkbook.SaveAs "C:\Users\" & Environ("Username") & "\" & Activesheet.Range("A2").Value & "\" & Activeworkbook.Name

rockybalboa
04-29-2014, 09:00 PM
Is not just



ActiveWorkbook.SaveAs "C:\Users\" & Environ("Username") & "\" & Activesheet.Range("A2").Value & "\" & Activeworkbook.Name



I have this code. But it is not saving the file in the folder.


Sub Test_Folder_Exist_With_Dir() Dim FolderPath As String
Dim TestStr As String


FolderPath = "C:\Users"
If Right(FolderPath, 1) <> "\" Then
FolderPath = FolderPath & "\"
End If


TestStr = ThisWorkbook.Sheets("Sheet1").Range("A2").Value
On Error Resume Next
TestStr = Dir(FolderPath)
On Error GoTo 0
If TestStr = "" Then
MsgBox "Folder doesn't exist"
Else
ActiveWorkbook.SaveCopyAs Filename:=FolderPath & ThisWorkbook.Sheets("Sheet1").Range("A2").Value & ".xlsm"
End If


End Sub

Ok i figured it out. Thanks

ActiveWorkbook.SaveCopyAs Filename:=FolderPath & ThisWorkbook.Sheets("Sheet1").Range("A2").Value & "\" & ThisWorkbook.Sheets("Sheet1").Range("A2").Value & ".xlsm"