-
Jan 2nd, 2018, 08:53 AM
#1
Thread Starter
Hyperactive Member
Inputbox in file path
Hi...I have a code which splits worksheets into individual workbooks and saves each workbook.
I have the file path in the code but the folder will change each month so I added an inputbox. But the problem I have is that the inputbox prompts for every workbook. I just want to set the same path for all the workbooks it saves.
The code for the filepath is [ fname = "C:\Reports\" & inputbox("Enter Folder") & "\" & wb.Sheets(1).Name & ".xlsx" ]
Any suggestions please?
-
Jan 2nd, 2018, 12:55 PM
#2
Re: Inputbox in file path
You need to ask the user for the path at the beginning of the macro, before you start the separate and save process. Save this to a variable that you will use to save the file.
Post the full code for the macro and we can find where to put it.
Example:
Code:
Option Explicit
Sub SaveSheets()
Dim strFolderName As String, fname as String
strFolderName = InputBox("enter Folder")
' Code to save sheets starts here
' Set File Name
fname = "C:\Reports\" & strFolderName & "\" & wb.Sheets(1).Name & ".xlsx"
' Additional Save code here
End Sub
Last edited by jdc2000; Jan 2nd, 2018 at 02:16 PM.
-
Jan 2nd, 2018, 03:15 PM
#3
Re: Inputbox in file path
you could also use a file dialog
Code:
with application.filedialog(mofiledialogfolderpicker)
.initialfilename = "c:\reports"
.show
myfolder = .selecteditems(1)
end with
then just append the filename to myfolder
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jan 3rd, 2018, 04:00 AM
#4
Thread Starter
Hyperactive Member
Re: Inputbox in file path
hi this is the last part of the code where the workbooks are saved. I'm not quite sure where to add the above code
Code:
Range("E5").ClearContents
sht.Move
Set wb = ActiveWorkbook
wb.Sheets(1).Name = Mid(wb.Sheets(1).Name, 14, 4)
ActiveSheet.Cells(1, 1).Select
fname = fname = "C:\Reports\" & inputbox("Enter Folder") & "\" & wb.Sheets(1).Name & ".xlsx"
If Len(Dir(fname)) > 0 Then Kill fname ' used for testing, saves getting file already exists dialog
wb.SaveAs fname
wb.Close
End If
Next
End Sub
-
Jan 3rd, 2018, 04:08 AM
#5
Re: Inputbox in file path
move the inputbox (or dialog) above the for (which you have not shown)
and change like
Code:
fname = fname = "C:\Reports\" & varfolder & "\" & wb.Sheets(1).Name & ".xlsx"
where varfolder is the return from inputbox
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jan 3rd, 2018, 04:26 AM
#6
Thread Starter
Hyperactive Member
Re: Inputbox in file path
Thanks Pete. Works great.
Last edited by fusion001; Jan 3rd, 2018 at 04:49 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|