Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem With Code...Still
I posted a message earlier and been trying to figure out why this code
doesn't work right. Thank you for all your help. Private Sub CommandButton6_Click() Dim message As String Set MyComputer = CreateObject("Scripting.FileSystemObject") FolderName1 = Range("F6") FileName1 = Range("M6") If MyComputer.FolderExists(FolderName1) = False Then message = MsgBox("The directory: " & FolderName1 & ", does not exist." & Chr(10) _ & "Enter a new directory path (cell F6).", 0, "Not today my friend.") Else If MyComputer.FileExists(FileName1) = True Then message = MsgBox("The file name: " & FileName1 & ", already exists." & Chr(10) _ & "A copy of this file has NOT been saved.", 0, "Not today my friend.") Else Sheet18.Visible = xlSheetVisible Sheets("Export").Copy ActiveWorkbook.SaveAs Filename:=FileName1, FileFormat:=xlNormal With Workbooks(FileName1).Sheets(1).Cells .Value = .Value End With ActiveWindow.Close End If End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem With Code...Still
Precisely what does "doesn't work" mean? Is it throwing an error? If so,
what is the error message? If not, then what does it do, or not do? When I looked at this before I could not ascertain what Filename1 equated to. To my knowledge that still has not been revealed. Does it include the complete path? You must remember that we cannot see your file so you must explain the circumstances and define what your variables are if you expect useable solutions. "NFL" wrote in message ... I posted a message earlier and been trying to figure out why this code doesn't work right. Thank you for all your help. Private Sub CommandButton6_Click() Dim message As String Set MyComputer = CreateObject("Scripting.FileSystemObject") FolderName1 = Range("F6") FileName1 = Range("M6") If MyComputer.FolderExists(FolderName1) = False Then message = MsgBox("The directory: " & FolderName1 & ", does not exist." & Chr(10) _ & "Enter a new directory path (cell F6).", 0, "Not today my friend.") Else If MyComputer.FileExists(FileName1) = True Then message = MsgBox("The file name: " & FileName1 & ", already exists." & Chr(10) _ & "A copy of this file has NOT been saved.", 0, "Not today my friend.") Else Sheet18.Visible = xlSheetVisible Sheets("Export").Copy ActiveWorkbook.SaveAs Filename:=FileName1, FileFormat:=xlNormal With Workbooks(FileName1).Sheets(1).Cells .Value = .Value End With ActiveWindow.Close End If End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem With Code...Still
I did not test this, but it should work if the values in F6 and M6 are valid
names and properly configured. If it does not work, then post back with any error messages received and what line of code is highlighted when the error occurs. Private Sub CommandButton6_Click() Dim message As String, FolderName1 As String, FileName1 As String Set MyComputer = CreateObject("Scripting.FileSystemObject") FolderName1 = Range("F6").Value FileName1 = Range("M6").Value If MyComputer.FolderExists(FolderName1) = False Then message = MsgBox("The directory: " & FolderName1 & _ ", does not exist." & Chr(10) & "Enter a new directory" & _ "path (cell F6).", 0, "Not today my friend.") ElseIf MyComputer.FileExists(FileName1) = True Then message = MsgBox("The file name: " & FileName1 & _ ", already exists." & Chr(10) & "A copy of this file" & _ "has NOT been saved.", 0, "Not today my friend.") Else Sheet18.Visible = xlSheetVisible Sheets("Export").Copy ActiveWorkbook.SaveAs Filename:=FileName1, FileFormat:=xlNormal With Workbooks(FileName1).Sheets(1).Cells .Value = .Value End With ActiveWorkbook.Close End If End If End Sub "NFL" wrote in message ... I posted a message earlier and been trying to figure out why this code doesn't work right. Thank you for all your help. Private Sub CommandButton6_Click() Dim message As String Set MyComputer = CreateObject("Scripting.FileSystemObject") FolderName1 = Range("F6") FileName1 = Range("M6") If MyComputer.FolderExists(FolderName1) = False Then message = MsgBox("The directory: " & FolderName1 & ", does not exist." & Chr(10) _ & "Enter a new directory path (cell F6).", 0, "Not today my friend.") Else If MyComputer.FileExists(FileName1) = True Then message = MsgBox("The file name: " & FileName1 & ", already exists." & Chr(10) _ & "A copy of this file has NOT been saved.", 0, "Not today my friend.") Else Sheet18.Visible = xlSheetVisible Sheets("Export").Copy ActiveWorkbook.SaveAs Filename:=FileName1, FileFormat:=xlNormal With Workbooks(FileName1).Sheets(1).Cells .Value = .Value End With ActiveWindow.Close End If End If End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem With Code...Still
OK NFL, I think I have figured out the problem. Your If Then ElseIf is
causing vba to skip the last two options if the first one is true. The code is a little confusing as written, but it looks as if you want to check if a file exists and if so, exit the process. If the file does not exist then create the file from sheet 18. If that is true then this should work. Private Sub CommandButton6_Click() Dim message As String, FolderName1 As String, FileName1 As String Set MyComputer = CreateObject("Scripting.FileSystemObject") FolderName1 = Range("F6").Value FileName1 = Range("M6").Value If MyComputer.FolderExists(FolderName1) = False Then message = MsgBox("The directory: " & FolderName1 & _ ", does not exist." & Chr(10) & "Enter a new directory" & _ "path (cell F6).", 0, "Not today my friend.") ElseIf MyComputer.FileExists(FileName1) = True Then message = MsgBox("The file name: " & FileName1 & _ ", already exists." & Chr(10) & "A copy of this file" & _ "has NOT been saved.", 0, "Not today my friend.") Exit Sub End If Sheet18.Visible = xlSheetVisible Sheets("Export").Copy ActiveWorkbook.SaveAs Filename:=FileName1, FileFormat:=xlNormal With Workbooks(FileName1).Sheets(1).Cells .Value = .Value End With ActiveWorkbook.Close SaveChanges:=True End Sub "JLGWhiz" wrote in message ... I did not test this, but it should work if the values in F6 and M6 are valid names and properly configured. If it does not work, then post back with any error messages received and what line of code is highlighted when the error occurs. Private Sub CommandButton6_Click() Dim message As String, FolderName1 As String, FileName1 As String Set MyComputer = CreateObject("Scripting.FileSystemObject") FolderName1 = Range("F6").Value FileName1 = Range("M6").Value If MyComputer.FolderExists(FolderName1) = False Then message = MsgBox("The directory: " & FolderName1 & _ ", does not exist." & Chr(10) & "Enter a new directory" & _ "path (cell F6).", 0, "Not today my friend.") ElseIf MyComputer.FileExists(FileName1) = True Then message = MsgBox("The file name: " & FileName1 & _ ", already exists." & Chr(10) & "A copy of this file" & _ "has NOT been saved.", 0, "Not today my friend.") Else Sheet18.Visible = xlSheetVisible Sheets("Export").Copy ActiveWorkbook.SaveAs Filename:=FileName1, FileFormat:=xlNormal With Workbooks(FileName1).Sheets(1).Cells .Value = .Value End With ActiveWorkbook.Close End If End If End Sub "NFL" wrote in message ... I posted a message earlier and been trying to figure out why this code doesn't work right. Thank you for all your help. Private Sub CommandButton6_Click() Dim message As String Set MyComputer = CreateObject("Scripting.FileSystemObject") FolderName1 = Range("F6") FileName1 = Range("M6") If MyComputer.FolderExists(FolderName1) = False Then message = MsgBox("The directory: " & FolderName1 & ", does not exist." & Chr(10) _ & "Enter a new directory path (cell F6).", 0, "Not today my friend.") Else If MyComputer.FileExists(FileName1) = True Then message = MsgBox("The file name: " & FileName1 & ", already exists." & Chr(10) _ & "A copy of this file has NOT been saved.", 0, "Not today my friend.") Else Sheet18.Visible = xlSheetVisible Sheets("Export").Copy ActiveWorkbook.SaveAs Filename:=FileName1, FileFormat:=xlNormal With Workbooks(FileName1).Sheets(1).Cells .Value = .Value End With ActiveWindow.Close End If End If End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem With Code...Still
Here's the summary of what is going on. The code below does create the file
to the designated path. Here are the values.... F6 = J:\Data\Export and the value of M6 = Now() Problem: When the file is exported, the formulas are being carried over to the new file. I do not want the formulas and references passed on. What happens is after the file is created I get a Run-time error '9': Subscript out of range error message. When I press the Debug button it takes me to this line.... With Workbooks(FileName1).Sheets(1).Cells Hope that makes sense and thank you for your reply. I've been trying several methods to make this work and I'm not getting anywhere. "JLGWhiz" wrote: I did not test this, but it should work if the values in F6 and M6 are valid names and properly configured. If it does not work, then post back with any error messages received and what line of code is highlighted when the error occurs. Private Sub CommandButton6_Click() Dim message As String, FolderName1 As String, FileName1 As String Set MyComputer = CreateObject("Scripting.FileSystemObject") FolderName1 = Range("F6").Value FileName1 = Range("M6").Value If MyComputer.FolderExists(FolderName1) = False Then message = MsgBox("The directory: " & FolderName1 & _ ", does not exist." & Chr(10) & "Enter a new directory" & _ "path (cell F6).", 0, "Not today my friend.") ElseIf MyComputer.FileExists(FileName1) = True Then message = MsgBox("The file name: " & FileName1 & _ ", already exists." & Chr(10) & "A copy of this file" & _ "has NOT been saved.", 0, "Not today my friend.") Else Sheet18.Visible = xlSheetVisible Sheets("Export").Copy ActiveWorkbook.SaveAs Filename:=FileName1, FileFormat:=xlNormal With Workbooks(FileName1).Sheets(1).Cells .Value = .Value End With ActiveWorkbook.Close End If End If End Sub "NFL" wrote in message ... I posted a message earlier and been trying to figure out why this code doesn't work right. Thank you for all your help. Private Sub CommandButton6_Click() Dim message As String Set MyComputer = CreateObject("Scripting.FileSystemObject") FolderName1 = Range("F6") FileName1 = Range("M6") If MyComputer.FolderExists(FolderName1) = False Then message = MsgBox("The directory: " & FolderName1 & ", does not exist." & Chr(10) _ & "Enter a new directory path (cell F6).", 0, "Not today my friend.") Else If MyComputer.FileExists(FileName1) = True Then message = MsgBox("The file name: " & FileName1 & ", already exists." & Chr(10) _ & "A copy of this file has NOT been saved.", 0, "Not today my friend.") Else Sheet18.Visible = xlSheetVisible Sheets("Export").Copy ActiveWorkbook.SaveAs Filename:=FileName1, FileFormat:=xlNormal With Workbooks(FileName1).Sheets(1).Cells .Value = .Value End With ActiveWindow.Close End If End If End Sub . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem With Code...Still
I tried the code and it looks cleaner. Thank you. The file does get created
like it is supposed to do and what happens afterwards is a Run-time error '9': Subscript out of range message shows up. When I remove the code .... With Workbooks(FileName1).Sheets(1).Cells .Value = .Value End With No errors will appear and the file will get created along with references and formulas. "JLGWhiz" wrote: OK NFL, I think I have figured out the problem. Your If Then ElseIf is causing vba to skip the last two options if the first one is true. If I remove the .. With Workbooks(FileName1).Sheets(1).Cells .Value = .Value End With The code is a little confusing as written, but it looks as if you want to check if a file exists and if so, exit the process. If the file does not exist then create the file from sheet 18. If that is true then this should work. Private Sub CommandButton6_Click() Dim message As String, FolderName1 As String, FileName1 As String Set MyComputer = CreateObject("Scripting.FileSystemObject") FolderName1 = Range("F6").Value FileName1 = Range("M6").Value If MyComputer.FolderExists(FolderName1) = False Then message = MsgBox("The directory: " & FolderName1 & _ ", does not exist." & Chr(10) & "Enter a new directory" & _ "path (cell F6).", 0, "Not today my friend.") ElseIf MyComputer.FileExists(FileName1) = True Then message = MsgBox("The file name: " & FileName1 & _ ", already exists." & Chr(10) & "A copy of this file" & _ "has NOT been saved.", 0, "Not today my friend.") Exit Sub End If Sheet18.Visible = xlSheetVisible Sheets("Export").Copy ActiveWorkbook.SaveAs Filename:=FileName1, FileFormat:=xlNormal With Workbooks(FileName1).Sheets(1).Cells .Value = .Value End With ActiveWorkbook.Close SaveChanges:=True End Sub "JLGWhiz" wrote in message ... I did not test this, but it should work if the values in F6 and M6 are valid names and properly configured. If it does not work, then post back with any error messages received and what line of code is highlighted when the error occurs. Private Sub CommandButton6_Click() Dim message As String, FolderName1 As String, FileName1 As String Set MyComputer = CreateObject("Scripting.FileSystemObject") FolderName1 = Range("F6").Value FileName1 = Range("M6").Value If MyComputer.FolderExists(FolderName1) = False Then message = MsgBox("The directory: " & FolderName1 & _ ", does not exist." & Chr(10) & "Enter a new directory" & _ "path (cell F6).", 0, "Not today my friend.") ElseIf MyComputer.FileExists(FileName1) = True Then message = MsgBox("The file name: " & FileName1 & _ ", already exists." & Chr(10) & "A copy of this file" & _ "has NOT been saved.", 0, "Not today my friend.") Else Sheet18.Visible = xlSheetVisible Sheets("Export").Copy ActiveWorkbook.SaveAs Filename:=FileName1, FileFormat:=xlNormal With Workbooks(FileName1).Sheets(1).Cells .Value = .Value End With ActiveWorkbook.Close End If End If End Sub "NFL" wrote in message ... I posted a message earlier and been trying to figure out why this code doesn't work right. Thank you for all your help. Private Sub CommandButton6_Click() Dim message As String Set MyComputer = CreateObject("Scripting.FileSystemObject") FolderName1 = Range("F6") FileName1 = Range("M6") If MyComputer.FolderExists(FolderName1) = False Then message = MsgBox("The directory: " & FolderName1 & ", does not exist." & Chr(10) _ & "Enter a new directory path (cell F6).", 0, "Not today my friend.") Else If MyComputer.FileExists(FileName1) = True Then message = MsgBox("The file name: " & FileName1 & ", already exists." & Chr(10) _ & "A copy of this file has NOT been saved.", 0, "Not today my friend.") Else Sheet18.Visible = xlSheetVisible Sheets("Export").Copy ActiveWorkbook.SaveAs Filename:=FileName1, FileFormat:=xlNormal With Workbooks(FileName1).Sheets(1).Cells .Value = .Value End With ActiveWindow.Close End If End If End Sub . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem With Code...Still
value of M6 = Now()
This can be a problem item. You are using Now() as a file name. One tenth of a second after you assign the value of Now() to a variable, the value of Now() is different. The variable still holds the original value of the time that it was assigned, but you will never find that value in another file name that was previously assigned, so looking for it is an exercise in futility. To shorten all that up to an understandiable explanation, Now() returns a value that is asnapshot of the current date and time such as: 5/27/2010 10:59:23 as a numeric data type. So the value of Now() is constantly changing as the clock ticks. In the case of your code, the value will change each time the worksheet is reactivated. That would be on Workbook.Open and selecting another sheet then returning to that sheet. So you will never be able to find a previously assigned filename the way the code is written, because the Now() value that is used as a file name is unique. You need to re-think this project and see if there is something more reliable you can use for a file name. the =Today() function only returns the date as a numeric data type, You might be able to use the Format function: FileName1 = Format(Range("M6").Value, "d/m/yyyy") & ".xls" That would give you a date as a string data type which you could then search for as a file name But as you have it now, FileName1 does not equate to a valid file name. "NFL" wrote in message ... Here's the summary of what is going on. The code below does create the file to the designated path. Here are the values.... F6 = J:\Data\Export and the value of M6 = Now() Problem: When the file is exported, the formulas are being carried over to the new file. I do not want the formulas and references passed on. What happens is after the file is created I get a Run-time error '9': Subscript out of range error message. When I press the Debug button it takes me to this line.... With Workbooks(FileName1).Sheets(1).Cells Hope that makes sense and thank you for your reply. I've been trying several methods to make this work and I'm not getting anywhere. "JLGWhiz" wrote: I did not test this, but it should work if the values in F6 and M6 are valid names and properly configured. If it does not work, then post back with any error messages received and what line of code is highlighted when the error occurs. Private Sub CommandButton6_Click() Dim message As String, FolderName1 As String, FileName1 As String Set MyComputer = CreateObject("Scripting.FileSystemObject") FolderName1 = Range("F6").Value FileName1 = Range("M6").Value If MyComputer.FolderExists(FolderName1) = False Then message = MsgBox("The directory: " & FolderName1 & _ ", does not exist." & Chr(10) & "Enter a new directory" & _ "path (cell F6).", 0, "Not today my friend.") ElseIf MyComputer.FileExists(FileName1) = True Then message = MsgBox("The file name: " & FileName1 & _ ", already exists." & Chr(10) & "A copy of this file" & _ "has NOT been saved.", 0, "Not today my friend.") Else Sheet18.Visible = xlSheetVisible Sheets("Export").Copy ActiveWorkbook.SaveAs Filename:=FileName1, FileFormat:=xlNormal With Workbooks(FileName1).Sheets(1).Cells .Value = .Value End With ActiveWorkbook.Close End If End If End Sub "NFL" wrote in message ... I posted a message earlier and been trying to figure out why this code doesn't work right. Thank you for all your help. Private Sub CommandButton6_Click() Dim message As String Set MyComputer = CreateObject("Scripting.FileSystemObject") FolderName1 = Range("F6") FileName1 = Range("M6") If MyComputer.FolderExists(FolderName1) = False Then message = MsgBox("The directory: " & FolderName1 & ", does not exist." & Chr(10) _ & "Enter a new directory path (cell F6).", 0, "Not today my friend.") Else If MyComputer.FileExists(FileName1) = True Then message = MsgBox("The file name: " & FileName1 & ", already exists." & Chr(10) _ & "A copy of this file has NOT been saved.", 0, "Not today my friend.") Else Sheet18.Visible = xlSheetVisible Sheets("Export").Copy ActiveWorkbook.SaveAs Filename:=FileName1, FileFormat:=xlNormal With Workbooks(FileName1).Sheets(1).Cells .Value = .Value End With ActiveWindow.Close End If End If End Sub . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with code (error code 9) | Excel Programming | |||
Code causes a problem | Excel Programming | |||
VBA Code Problem | Excel Programming | |||
Code Problem | Excel Programming | |||
Problem with VBA Code? | Excel Programming |