Userform Save data To Worksheet & File

  • Thread starter Thread starter Moose
  • Start date Start date
M

Moose

I have a user form that is used to input new Job Names & Job Numbers
into a worksheet called JobName within the existing workbook using
text boxes this part works fine. What I would like to know is it
posssible that when I click the add button to insert the Job Name &
Job Number into the worksheet called JobName can the Job Number be
saved into a folder called Project Costs in my documents as an .xls
file (Example as 001.xls)

The path to My Documents is mydir= "C:\Users\Craig\Documents\Project
Costs"

The reason is that I have another userform with a command button and
text box to search for workbooks and open by Job Number using the
following code

Private Sub CommandButton12_Click()

Dim mydir As String

mydir = "C:\Users\Craig\Documents\Project Costs"
If Dir(mydir & "\" & TextBox1.Value & ".xls") <> "" Then
Workbooks.Open Filename:=mydir & "\" & TextBox1.Value & ".xls"
Unload Me
Else
MsgBox "File: " & TextBox1.Value & "Job Number Not Found"
End If

End Sub
 
Here is some code to get you basically what you want. Instead of hard
coding the string names, you could replace with
Me.TextBox1.Value or whatever your case is.

Sub addfilefolder()
Dim folderPath As String
Dim fileName As String

folderPath = "C:\Users\kurtz\Desktop\test"
fileName = TextBoxJobNumber.Value & ".xlsx"
Workbooks.Add
MkDir folderPath
ActiveWorkbook.SaveAs Filename:=folderPath & "\" & fileName
Workbooks(fileName).Close

End Sub


Hope this helps
dk
 
Back
Top