| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create New Folder
My below code is bombing on the next to last line "Thisworkbook.SaveAs..." Can anyone spot my problem?, please. TIA Sub Writingfilenames() Dim foldername As String Dim Filename As String foldername = Sheets("ADSS-01").Name If Not FolderExists(foldername) Then MkDir foldername Filename = Sheets("ADSS-01").Cells(3, 4) ThisWorkbook.SaveAs Filename:="C:\My Documents\" & foldername & "\" & Filename & ".xls" End Sub '----------------------------------------------------------------- Function FolderExists(Folder) As Boolean '----------------------------------------------------------------- Dim sFolder As String On Error Resume Next sFolder = Dir(Folder, vbDirectory) If sFolder < "" Then If (GetAttr(sFolder) And vbDirectory) = vbDirectory Then FolderExists = True End If End If End Function |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Create New Folder
At the time the macro breaks does the folder C:\My Documents\ADSS-01" exist?
It seems to me that a possible flaw is that you're assuming that C:\My Documents is the active folder when the macro is run. -- Jim Rech Excel MVP "JMay" wrote in message news:RYBxc.462$tI2.182@fed1read07... | | My below code is bombing on the next to last line "Thisworkbook.SaveAs..." | Can anyone spot my problem?, please. | TIA | | | Sub Writingfilenames() | Dim foldername As String | Dim Filename As String | foldername = Sheets("ADSS-01").Name | If Not FolderExists(foldername) Then MkDir foldername | Filename = Sheets("ADSS-01").Cells(3, 4) | ThisWorkbook.SaveAs Filename:="C:\My Documents\" & foldername & "\" & | Filename & ".xls" | End Sub | | '----------------------------------------------------------------- | Function FolderExists(Folder) As Boolean | '----------------------------------------------------------------- | Dim sFolder As String | On Error Resume Next | sFolder = Dir(Folder, vbDirectory) | If sFolder < "" Then | If (GetAttr(sFolder) And vbDirectory) = vbDirectory Then | FolderExists = True | End If | End If | End Function | | |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Create New Folder
Your code doesn't appear to do anything to check for the folder as a sub
folder of "C:\My Documents\" nor to create a folder in that location. Sub Writingfilenames() Dim Filename As String On Error Resume Net Mkdir "C:\My Documents\" & Sheets("ADSS-01").Name On Error goto 0 Filename = Sheets("ADSS-01").Cells(3, 4).Value ThisWorkbook.SaveAs Filename:="C:\My Documents\" & foldername & "\" & Filename & ".xls" End Sub Isn't the result of Sheets("ADSS-01").Name just "ADSS-01" -- Regards, Tom Ogilvy "JMay" wrote in message news:RYBxc.462$tI2.182@fed1read07... My below code is bombing on the next to last line "Thisworkbook.SaveAs..." Can anyone spot my problem?, please. TIA Sub Writingfilenames() Dim foldername As String Dim Filename As String foldername = Sheets("ADSS-01").Name If Not FolderExists(foldername) Then MkDir foldername Filename = Sheets("ADSS-01").Cells(3, 4) ThisWorkbook.SaveAs Filename:="C:\My Documents\" & foldername & "\" & Filename & ".xls" End Sub '----------------------------------------------------------------- Function FolderExists(Folder) As Boolean '----------------------------------------------------------------- Dim sFolder As String On Error Resume Next sFolder = Dir(Folder, vbDirectory) If sFolder < "" Then If (GetAttr(sFolder) And vbDirectory) = vbDirectory Then FolderExists = True End If End If End Function |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Create New Folder
Thanks Jim
No, "C:\My Documents\ADSS-01" does not exist. "Jim Rech" wrote in message ... At the time the macro breaks does the folder C:\My Documents\ADSS-01" exist? It seems to me that a possible flaw is that you're assuming that C:\My Documents is the active folder when the macro is run. -- Jim Rech Excel MVP "JMay" wrote in message news:RYBxc.462$tI2.182@fed1read07... | | My below code is bombing on the next to last line "Thisworkbook.SaveAs..." | Can anyone spot my problem?, please. | TIA | | | Sub Writingfilenames() | Dim foldername As String | Dim Filename As String | foldername = Sheets("ADSS-01").Name | If Not FolderExists(foldername) Then MkDir foldername | Filename = Sheets("ADSS-01").Cells(3, 4) | ThisWorkbook.SaveAs Filename:="C:\My Documents\" & foldername & "\" & | Filename & ".xls" | End Sub | | '----------------------------------------------------------------- | Function FolderExists(Folder) As Boolean | '----------------------------------------------------------------- | Dim sFolder As String | On Error Resume Next | sFolder = Dir(Folder, vbDirectory) | If sFolder < "" Then | If (GetAttr(sFolder) And vbDirectory) = vbDirectory Then | FolderExists = True | End If | End If | End Function | | |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Create New Folder
Isn't the result of Sheets("ADSS-01").Name just "ADSS-01"
Yes, "Tom Ogilvy" wrote in message ... Your code doesn't appear to do anything to check for the folder as a sub folder of "C:\My Documents\" nor to create a folder in that location. Sub Writingfilenames() Dim Filename As String On Error Resume Net Mkdir "C:\My Documents\" & Sheets("ADSS-01").Name On Error goto 0 Filename = Sheets("ADSS-01").Cells(3, 4).Value ThisWorkbook.SaveAs Filename:="C:\My Documents\" & foldername & "\" & Filename & ".xls" End Sub Isn't the result of Sheets("ADSS-01").Name just "ADSS-01" -- Regards, Tom Ogilvy "JMay" wrote in message news:RYBxc.462$tI2.182@fed1read07... My below code is bombing on the next to last line "Thisworkbook.SaveAs..." Can anyone spot my problem?, please. TIA Sub Writingfilenames() Dim foldername As String Dim Filename As String foldername = Sheets("ADSS-01").Name If Not FolderExists(foldername) Then MkDir foldername Filename = Sheets("ADSS-01").Cells(3, 4) ThisWorkbook.SaveAs Filename:="C:\My Documents\" & foldername & "\" & Filename & ".xls" End Sub '----------------------------------------------------------------- Function FolderExists(Folder) As Boolean '----------------------------------------------------------------- Dim sFolder As String On Error Resume Next sFolder = Dir(Folder, vbDirectory) If sFolder < "" Then If (GetAttr(sFolder) And vbDirectory) = vbDirectory Then FolderExists = True End If End If End Function |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Create New Folder
Replace with your modified code below (noting that you are not using
function call)... but anyway I'm still getting run-time error 1004 on line ThisWorkbook.SaveAs Filename:="C:\My Documents\" & foldername & "\" & Filename & ".xls" Thoughts? TIA "Tom Ogilvy" wrote in message ... Your code doesn't appear to do anything to check for the folder as a sub folder of "C:\My Documents\" nor to create a folder in that location. Sub Writingfilenames() Dim Filename As String On Error Resume Net Mkdir "C:\My Documents\" & Sheets("ADSS-01").Name On Error goto 0 Filename = Sheets("ADSS-01").Cells(3, 4).Value ThisWorkbook.SaveAs Filename:="C:\My Documents\" & foldername & "\" & Filename & ".xls" End Sub Isn't the result of Sheets("ADSS-01").Name just "ADSS-01" -- Regards, Tom Ogilvy "JMay" wrote in message news:RYBxc.462$tI2.182@fed1read07... My below code is bombing on the next to last line "Thisworkbook.SaveAs..." Can anyone spot my problem?, please. TIA Sub Writingfilenames() Dim foldername As String Dim Filename As String foldername = Sheets("ADSS-01").Name If Not FolderExists(foldername) Then MkDir foldername Filename = Sheets("ADSS-01").Cells(3, 4) ThisWorkbook.SaveAs Filename:="C:\My Documents\" & foldername & "\" & Filename & ".xls" End Sub '----------------------------------------------------------------- Function FolderExists(Folder) As Boolean '----------------------------------------------------------------- Dim sFolder As String On Error Resume Next sFolder = Dir(Folder, vbDirectory) If sFolder < "" Then If (GetAttr(sFolder) And vbDirectory) = vbDirectory Then FolderExists = True End If End If End Function |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Create New Folder
Sub Writingfilenames()
Dim Filename As String Dim FolderName As String On Error Resume Next FolderName = "C:\My Documents\ADSS-01" MkDir FolderName On Error GoTo 0 Filename = Sheets("ADSS-01").Cells(3, 4).Value ThisWorkbook.SaveAs Filename:=FolderName & _ Filename & ".xls" End Sub Worked fine for me. -- Regards, Tom Ogilvy "JMay" wrote in message news:HDDxc.64363$Yr.16682@okepread04... Replace with your modified code below (noting that you are not using function call)... but anyway I'm still getting run-time error 1004 on line ThisWorkbook.SaveAs Filename:="C:\My Documents\" & foldername & "\" & Filename & ".xls" Thoughts? TIA "Tom Ogilvy" wrote in message ... Your code doesn't appear to do anything to check for the folder as a sub folder of "C:\My Documents\" nor to create a folder in that location. Sub Writingfilenames() Dim Filename As String On Error Resume Net Mkdir "C:\My Documents\" & Sheets("ADSS-01").Name On Error goto 0 Filename = Sheets("ADSS-01").Cells(3, 4).Value ThisWorkbook.SaveAs Filename:="C:\My Documents\" & foldername & "\" & Filename & ".xls" End Sub Isn't the result of Sheets("ADSS-01").Name just "ADSS-01" -- Regards, Tom Ogilvy "JMay" wrote in message news:RYBxc.462$tI2.182@fed1read07... My below code is bombing on the next to last line "Thisworkbook.SaveAs..." Can anyone spot my problem?, please. TIA Sub Writingfilenames() Dim foldername As String Dim Filename As String foldername = Sheets("ADSS-01").Name If Not FolderExists(foldername) Then MkDir foldername Filename = Sheets("ADSS-01").Cells(3, 4) ThisWorkbook.SaveAs Filename:="C:\My Documents\" & foldername & "\" & Filename & ".xls" End Sub '----------------------------------------------------------------- Function FolderExists(Folder) As Boolean '----------------------------------------------------------------- Dim sFolder As String On Error Resume Next sFolder = Dir(Folder, vbDirectory) If sFolder < "" Then If (GetAttr(sFolder) And vbDirectory) = vbDirectory Then FolderExists = True End If End If End Function |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Create Folder Using VBA | Excel Discussion (Misc queries) | |||
| Create Folder and Text File in folder | Excel Programming | |||
| Create a Folder | Excel Programming | |||
| Create Folder | Excel Programming | |||
| Create Folder / Copy Folder / Replace | Excel Programming | |||
Linear Mode
