Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filesystemobject Help
I am trying to add files to a folder that already exist,which was working
fine until I found out the the folder name was not TAPECOMPARE,but is Tape Tracking 05-2005. I have coded strFolder = Left(strfile, Len(strFile -2),which will remove the date portion of the folder to only show the month. Now how do I get the month,dash and year to all show up for that folder. Will I have to hardcode the year in when it changes. The code I am using is listed below: Private Sub cmdDone_Click() Const cstrpath As String = "C:\Documents and Settings\Owner\My Documents\" Dim fso As Scripting.FileSystemObject Dim fld As Scripting.Folder Dim strFile As String Dim strDate As String Dim strFolder As String Dim wkb As Excel.Workbook strFile = "TapeCompare" strFile = strFile & Replace(Me.txtFileNameDate, " ", "") strFolder = Left(strFile, Len(strFile) - 2) Set fso = New Scripting.FileSystemObject If Not fso.FolderExists(cstrpath & strFolder) Then MkDir (cstrpath & strFolder) End If 'Set fld = fso.GetFolder(cstrpath & strFolder) Set wkb = Excel.ActiveWorkbook wkb.SaveAs cstrpath & strFolder & "\" & strFile & ".xls" 'wkb.SaveAs cstrpath & "Cynthia Grover.xls" MsgBox "file has been Saved As: " & wkb.Name & "In Folder" & cstrpath & strFolder Set wkb = Nothing Unload Me End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filesystemobject Help
What does me.TextFileNameDate look like?
-- Regards, Tom Ogilvy "CyndyG" wrote in message ... I am trying to add files to a folder that already exist,which was working fine until I found out the the folder name was not TAPECOMPARE,but is Tape Tracking 05-2005. I have coded strFolder = Left(strfile, Len(strFile -2),which will remove the date portion of the folder to only show the month. Now how do I get the month,dash and year to all show up for that folder. Will I have to hardcode the year in when it changes. The code I am using is listed below: Private Sub cmdDone_Click() Const cstrpath As String = "C:\Documents and Settings\Owner\My Documents\" Dim fso As Scripting.FileSystemObject Dim fld As Scripting.Folder Dim strFile As String Dim strDate As String Dim strFolder As String Dim wkb As Excel.Workbook strFile = "TapeCompare" strFile = strFile & Replace(Me.txtFileNameDate, " ", "") strFolder = Left(strFile, Len(strFile) - 2) Set fso = New Scripting.FileSystemObject If Not fso.FolderExists(cstrpath & strFolder) Then MkDir (cstrpath & strFolder) End If 'Set fld = fso.GetFolder(cstrpath & strFolder) Set wkb = Excel.ActiveWorkbook wkb.SaveAs cstrpath & strFolder & "\" & strFile & ".xls" 'wkb.SaveAs cstrpath & "Cynthia Grover.xls" MsgBox "file has been Saved As: " & wkb.Name & "In Folder" & cstrpath & strFolder Set wkb = Nothing Unload Me End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filesystemobject Help
me.txtFileNameDate will end of looking like this afet the user key in the
month and date on the userform....TapeCompar0515 ,that will go in the Tape Tracking folder. Here is that procedure : Private Sub cmdSaveFileName_Click() Dim wkb As Excel.Workbook Set wkb = Excel.ActiveWorkbook 'strWork = Right(wkb.Name, 17) If Left(wkb.Name, 11) = "TapeCompare" And IsNumeric(Mid(wkb.Name, 12, 4)) And Len(wkb.Name) = 19 Then wkb.Save Else Load FileNameAndDate FileNameAndDate!txtFileNameDate.SelStart = 0 FileNameAndDate!txtFileNameDate.SelLength = Len(FileNameAndDate.txtFileNameDate) FileNameAndDate.Show End If Set wkb = Nothing End Sub "Tom Ogilvy" wrote: What does me.TextFileNameDate look like? -- Regards, Tom Ogilvy "CyndyG" wrote in message ... I am trying to add files to a folder that already exist,which was working fine until I found out the the folder name was not TAPECOMPARE,but is Tape Tracking 05-2005. I have coded strFolder = Left(strfile, Len(strFile -2),which will remove the date portion of the folder to only show the month. Now how do I get the month,dash and year to all show up for that folder. Will I have to hardcode the year in when it changes. The code I am using is listed below: Private Sub cmdDone_Click() Const cstrpath As String = "C:\Documents and Settings\Owner\My Documents\" Dim fso As Scripting.FileSystemObject Dim fld As Scripting.Folder Dim strFile As String Dim strDate As String Dim strFolder As String Dim wkb As Excel.Workbook strFile = "TapeCompare" strFile = strFile & Replace(Me.txtFileNameDate, " ", "") strFolder = Left(strFile, Len(strFile) - 2) Set fso = New Scripting.FileSystemObject If Not fso.FolderExists(cstrpath & strFolder) Then MkDir (cstrpath & strFolder) End If Set wkb = Excel.ActiveWorkbook wkb.SaveAs cstrpath & strFolder & "\" & strFile & ".xls" MsgBox "file has been Saved As: " & wkb.Name & "In Folder" & cstrpath & strFolder Set wkb = Nothing Unload Me End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filesystemobject Help
Your trying to turn
TapeCompar0515 into Tape Tracking 05-2005 ??? I assume the 05 in the first is the month. How do you intend to determine the year? If this is what you are doing: s = "TapeCompar0515" strFile = strFile & Replace(s, " ", "") ? strFile TapeCompar0515 strFolder = Left(strFile, Len(strFile) - 2) ? strFolder TapeCompar05 then to change strFolder to Tape Tracking 05- strFolder = Replace(strFolder,"TapeCompar","Tape Tracking") strFolder = Replace(strFolder,"TapeCompar","Tape Tracking") ? strFolder Tape Tracking05 Then you append strFolder = strFolder & "-" & year(date) modify year(date) to a method that will produce the proper year. -- Regards, Tom Ogilvy -- Regards, Tom Ogilvy "CyndyG" wrote in message ... me.txtFileNameDate will end of looking like this afet the user key in the month and date on the userform....TapeCompar0515 ,that will go in the Tape Tracking folder. Here is that procedure : Private Sub cmdSaveFileName_Click() Dim wkb As Excel.Workbook Set wkb = Excel.ActiveWorkbook 'strWork = Right(wkb.Name, 17) If Left(wkb.Name, 11) = "TapeCompare" And IsNumeric(Mid(wkb.Name, 12, 4)) And Len(wkb.Name) = 19 Then wkb.Save Else Load FileNameAndDate FileNameAndDate!txtFileNameDate.SelStart = 0 FileNameAndDate!txtFileNameDate.SelLength = Len(FileNameAndDate.txtFileNameDate) FileNameAndDate.Show End If Set wkb = Nothing End Sub "Tom Ogilvy" wrote: What does me.TextFileNameDate look like? -- Regards, Tom Ogilvy "CyndyG" wrote in message ... I am trying to add files to a folder that already exist,which was working fine until I found out the the folder name was not TAPECOMPARE,but is Tape Tracking 05-2005. I have coded strFolder = Left(strfile, Len(strFile -2),which will remove the date portion of the folder to only show the month. Now how do I get the month,dash and year to all show up for that folder. Will I have to hardcode the year in when it changes. The code I am using is listed below: Private Sub cmdDone_Click() Const cstrpath As String = "C:\Documents and Settings\Owner\My Documents\" Dim fso As Scripting.FileSystemObject Dim fld As Scripting.Folder Dim strFile As String Dim strDate As String Dim strFolder As String Dim wkb As Excel.Workbook strFile = "TapeCompare" strFile = strFile & Replace(Me.txtFileNameDate, " ", "") strFolder = Left(strFile, Len(strFile) - 2) Set fso = New Scripting.FileSystemObject If Not fso.FolderExists(cstrpath & strFolder) Then MkDir (cstrpath & strFolder) End If Set wkb = Excel.ActiveWorkbook wkb.SaveAs cstrpath & strFolder & "\" & strFile & ".xls" MsgBox "file has been Saved As: " & wkb.Name & "In Folder" & cstrpath & strFolder Set wkb = Nothing Unload Me End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filesystemobject Help
Thank You.
"Tom Ogilvy" wrote: Your trying to turn TapeCompar0515 into Tape Tracking 05-2005 ??? I assume the 05 in the first is the month. How do you intend to determine the year? If this is what you are doing: s = "TapeCompar0515" strFile = strFile & Replace(s, " ", "") ? strFile TapeCompar0515 strFolder = Left(strFile, Len(strFile) - 2) ? strFolder TapeCompar05 then to change strFolder to Tape Tracking 05- strFolder = Replace(strFolder,"TapeCompar","Tape Tracking") strFolder = Replace(strFolder,"TapeCompar","Tape Tracking") ? strFolder Tape Tracking05 Then you append strFolder = strFolder & "-" & year(date) modify year(date) to a method that will produce the proper year. -- Regards, Tom Ogilvy -- Regards, Tom Ogilvy "CyndyG" wrote in message ... me.txtFileNameDate will end of looking like this afet the user key in the month and date on the userform....TapeCompar0515 ,that will go in the Tape Tracking folder. Here is that procedure : Private Sub cmdSaveFileName_Click() Dim wkb As Excel.Workbook Set wkb = Excel.ActiveWorkbook 'strWork = Right(wkb.Name, 17) If Left(wkb.Name, 11) = "TapeCompare" And IsNumeric(Mid(wkb.Name, 12, 4)) And Len(wkb.Name) = 19 Then wkb.Save Else Load FileNameAndDate FileNameAndDate!txtFileNameDate.SelStart = 0 FileNameAndDate!txtFileNameDate.SelLength = Len(FileNameAndDate.txtFileNameDate) FileNameAndDate.Show End If Set wkb = Nothing End Sub "Tom Ogilvy" wrote: What does me.TextFileNameDate look like? -- Regards, Tom Ogilvy "CyndyG" wrote in message ... I am trying to add files to a folder that already exist,which was working fine until I found out the the folder name was not TAPECOMPARE,but is Tape Tracking 05-2005. I have coded strFolder = Left(strfile, Len(strFile -2),which will remove the date portion of the folder to only show the month. Now how do I get the month,dash and year to all show up for that folder. Will I have to hardcode the year in when it changes. The code I am using is listed below: Private Sub cmdDone_Click() Const cstrpath As String = "C:\Documents and Settings\Owner\My Documents\" Dim fso As Scripting.FileSystemObject Dim fld As Scripting.Folder Dim strFile As String Dim strDate As String Dim strFolder As String Dim wkb As Excel.Workbook strFile = "TapeCompare" strFile = strFile & Replace(Me.txtFileNameDate, " ", "") strFolder = Left(strFile, Len(strFile) - 2) Set fso = New Scripting.FileSystemObject If Not fso.FolderExists(cstrpath & strFolder) Then MkDir (cstrpath & strFolder) End If Set wkb = Excel.ActiveWorkbook wkb.SaveAs cstrpath & strFolder & "\" & strFile & ".xls" MsgBox "file has been Saved As: " & wkb.Name & "In Folder" & cstrpath & strFolder Set wkb = Nothing Unload Me End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
filesystemobject | Excel Programming | |||
filesystemobject | Excel Programming | |||
Help using FileSystemObject CopyFile | Excel Programming | |||
FileSystemObject | Excel Programming | |||
Help with FileSystemObject? | Excel Programming |