D
David
I have a sheet in my workbook (written in Excel 2003), that is bombing when
run in Excel 2007. The purpose is to let the user enter data on uncompleted
jobs, save the sheet (with the job name) and to allow them to import the job
later and update the data until the job is done. They can have 100's of jobs.
I suspect this is an issue with the naming convention in 2007, but I don't
have Excel 2007 to test. Can someone help figure out what the issue is and
fix it so that it will work in both 2003 and 2007 of offer a better
suggestion (I looked at just doing a CSV/TXT file but can't figure out how to
export/import just the cells with the data points and leave the formulas
alone)? Thanks so much! Here is the code:
Dim wsoutput As Worksheet
Dim JNum As String
Dim MyDirectory As String
Dim DirTest As String
Dim DefPath As String
'Checks to See If A Directory Exists, If Not, Creates It
MyDirectory = ActiveWorkbook.Path & "\" & "2009 Saved Jobs"
DirTest = Dir$(MyDirectory, vbDirectory)
If DirTest = "" Then
MkDir MyDirectory
DoEvents 'just to make sure it is there
End If
ChDir MyDirectory
DefPath = MyDirectory
If Right(DefPath, 1) <> "\" Then
DefPath = DefPath & "\"
End If
Set wsoutput = Sheet96
JNum = Sheet96.Range("E5")
wsoutput.Select
wsoutput.Copy
Cells.Select
Range("A1").Activate
ActiveWorkbook.Colors(53) = RGB(247, 252, 255)
Range("A1").Select
If Range("H42") = 0 Then
Do
FName = Application.GetSaveAsFilename("Job " & JNum, filefilter:="Excel
Files (*.xls), *.xls")
Loop Until FName <> False Or msoButtonSetCancel
Else
Do
FName = Application.GetSaveAsFilename("Job " & JNum & "C",
filefilter:="Excel Files (*.xls), *.xls")
Loop Until FName <> False Or msoButtonSetCancel
End If
If FName <> False Then
ActiveWorkbook.SaveAs FName
ActiveWorkbook.Close SaveChanges:=False
Else: Exit Sub
End If
ChDir CurDir & "\.."
'ActiveWindow.Close
End Sub
run in Excel 2007. The purpose is to let the user enter data on uncompleted
jobs, save the sheet (with the job name) and to allow them to import the job
later and update the data until the job is done. They can have 100's of jobs.
I suspect this is an issue with the naming convention in 2007, but I don't
have Excel 2007 to test. Can someone help figure out what the issue is and
fix it so that it will work in both 2003 and 2007 of offer a better
suggestion (I looked at just doing a CSV/TXT file but can't figure out how to
export/import just the cells with the data points and leave the formulas
alone)? Thanks so much! Here is the code:
Dim wsoutput As Worksheet
Dim JNum As String
Dim MyDirectory As String
Dim DirTest As String
Dim DefPath As String
'Checks to See If A Directory Exists, If Not, Creates It
MyDirectory = ActiveWorkbook.Path & "\" & "2009 Saved Jobs"
DirTest = Dir$(MyDirectory, vbDirectory)
If DirTest = "" Then
MkDir MyDirectory
DoEvents 'just to make sure it is there
End If
ChDir MyDirectory
DefPath = MyDirectory
If Right(DefPath, 1) <> "\" Then
DefPath = DefPath & "\"
End If
Set wsoutput = Sheet96
JNum = Sheet96.Range("E5")
wsoutput.Select
wsoutput.Copy
Cells.Select
Range("A1").Activate
ActiveWorkbook.Colors(53) = RGB(247, 252, 255)
Range("A1").Select
If Range("H42") = 0 Then
Do
FName = Application.GetSaveAsFilename("Job " & JNum, filefilter:="Excel
Files (*.xls), *.xls")
Loop Until FName <> False Or msoButtonSetCancel
Else
Do
FName = Application.GetSaveAsFilename("Job " & JNum & "C",
filefilter:="Excel Files (*.xls), *.xls")
Loop Until FName <> False Or msoButtonSetCancel
End If
If FName <> False Then
ActiveWorkbook.SaveAs FName
ActiveWorkbook.Close SaveChanges:=False
Else: Exit Sub
End If
ChDir CurDir & "\.."
'ActiveWindow.Close
End Sub