Code corrupts file (XL2003)

I have code (below) that opens and processes a separate raw data file, 
segregates data into several different worksheets in that data file, then 
saves the file.

I just found out that my code appears to corrupt the output files, which 
then cannot be opened in Excel2003, but can be opened in Excel2007. I can 
open *other* Excel files without a problem, but files created with this sub 
are corrupt. I suspect that it has to do with how the file is saved, so I'm 
posting just that snippet first, then the full code underneath. Should I be 
more restrictive in the Filefilter parameter? Or do I need to forceably add 
the ".xls" extension even though the save dialogue shows it to be saving as 
an XLS file? The file does save with the xls extension, and looks like an XL 
file in windows explorer (opens in Excel2003 when double clicked, but then 
throws an 'unrecognizable format' error, and shows a worksheet filled with 
ASCII characters).

Any advice or suggestions would be greatly appreciated.


Snippet:

DirectorySetPath ("\\wsak1\Perf\Score\Ops\Public\CurrentData")
new2fn = Application.GetSaveAsFilename( _
            InitialFileName:="2010 USA Ops Salary Increases - " & 
PasteMonthNum & " " & StrMonth, _
            FileFilter:="Excel-Compatible Files, *.xls;*.xlsx;*.xlsm;*.csv", _
            Title:=TitleString)

ActiveWorkbook.SaveAs Filename:=new2fn



Full code:

Sub MakeReferenceWkbk()

'default start path, editable by user from the filepicker dialogue
PathOnly = "\\wsak1\Perf\Score\Ops\People\Increases"

Dim I As Integer
Dim owb As Workbook 'original/main
Dim twb As Workbook 'temp/data file
Dim ows As Worksheet
Dim tws As Worksheet

Set owb = ActiveWorkbook
Set ows = ActiveWorkbook.ActiveSheet

Dim SaveDriveDir As String
'save default path
SaveDriveDir = CurDir

TitleString = "Please select the Raw data file"

    'change to new path
    DirectorySetPath (PathOnly)
    
    'get the file
    newFN = Application.GetOpenFilename(FileFilter:="Excel-Compatible Files, 
*.xls;*.xlsx;*.xlsm;*.csv", Title:=TitleString)
    If newFN = False Then
        ' They pressed Cancel
        MsgBox "Stopping because you did not select a file"
        'return to original default path
        DirectorySetPath (SaveDriveDir)
        Exit Sub
    Else
        MyFullFilePath = newFN
    End If

Application.StatusBar = "Opening File " & MyFullFilePath
    
'Open source workbook
Application.DisplayAlerts = False
Set twb = Workbooks.Open(Filename:=MyFullFilePath, UpdateLinks:=0, 
ReadOnly:=True)
Application.DisplayAlerts = True
twb.Activate
twb.Sheets(1).Activate

'update the file
    For I = 1 To 6
        ActiveWorkbook.Sheets.Add
    Next
    
    shtNameArr = Array("NWR", "NAR", "SWR", "SAR", "WWR")
    
    For I = 1 To 5
        ActiveWorkbook.Sheets(7).Select
        ActiveWorkbook.Sheets(7).Cells.Select
        Selection.AutoFilter
        Selection.AutoFilter Field:=9, Criteria1:=shtNameArr(I)
        ActiveWorkbook.Sheets(7).Cells.Select
        Selection.Copy
        ActiveWorkbook.Sheets(I).Select
        ActiveSheet.Paste
        LRow = lastRow(Sheets(I))
        ActiveSheet.Name = shtNameArr(I)
        ActiveSheet.Cells.Select
        ActiveSheet.Cells.EntireColumn.AutoFit
        ActiveSheet.Range("V1").Select
        Application.CutCopyMode = False
        ActiveCell.Value = "Days Late"
        ActiveSheet.Range("V2").Select
        ActiveCell.Formula = "=IF(M2>L2,M2-L2,"""")"
        ActiveSheet.Range("V2").Select
        Selection.AutoFill Destination:=Range("V2:V" & LRow)
        ActiveSheet.Range("A1").Select
    Next
    
        ActiveWorkbook.Sheets(7).Select
        ActiveWorkbook.Sheets(7).Cells.Select
        Selection.AutoFilter
        Selection.AutoFilter Field:=9, Criteria1:="STTC"
        Selection.AutoFilter Field:=18, Criteria1:="=03", Operator:=xlOr, 
Criteria2:="=04"
        ActiveWorkbook.Sheets(7).Cells.Select
        Selection.Copy
        ActiveWorkbook.Sheets(6).Select
        ActiveSheet.Paste
        LRow = lastRow(ActiveWorkbook.Sheets(6))
        ActiveSheet.Name = "STTC"
        ActiveSheet.Cells.Select
        ActiveSheet.Cells.EntireColumn.AutoFit
        ActiveSheet.Range("V1").Select
        Application.CutCopyMode = False
        ActiveCell.Value = "Days Late"
        ActiveSheet.Range("V2").Select
        ActiveCell.Formula = "=IF(M2>L2,M2-L2,"""")"
        ActiveSheet.Range("V2").Select
        Selection.AutoFill Destination:=Range("V2:V" & LRow)
        ActiveSheet.Range("A1").Select
    
    ActiveWorkbook.Sheets(1).Activate
    Application.DisplayAlerts = False
    ActiveWorkbook.Sheets(7).Delete
    Application.DisplayAlerts = True
  
'Select/copy a single cell to avoid clipboard warnings
ActiveSheet.Range("A1").Copy
    
''close the workbook to get it out of the way
'Application.DisplayAlerts = False 'just in case the clipboard trick doesn't 
work
'twb.Close SaveChanges:=False
'Application.DisplayAlerts = True

Application.StatusBar = False

sDate = Year(Now()) & Format(Month(Now()), "00") & Format(Day(Now()), "00")
ShortFileName = ExtractFileName(MyFullFilePath)

'get the month "name" for the data set being saved, to put it in the filename
PasteMonths = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
PasteMonths2 = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", 
"Sep", "Oct", "Nov", "Dec")
PasteMonthNum = CInt(InputBox("Enter the month number represented by this 
data (e.g. 1 for Jan, 2 for Feb, etc)", "Enter Data Month"))
If IsError(Application.Match(PasteMonthNum, PasteMonths, False)) Then
    MsgBox "Unable to recognize a date from 1 to 12." & Chr(13) & Chr(13) & 
"Output file not saved; please run again to finish processing", , "Month 
Number Error"
    Exit Sub
Else
    StrMonth = PasteMonths2(PasteMonthNum)
End If

DirectorySetPath ("\\wsak1\Perf\Score\Ops\Public\CurrentData")
'GetSaveAsFilename
new2fn = Application.GetSaveAsFilename( _
            InitialFileName:="2010 Ops Increases - " & PasteMonthNum & " " & 
StrMonth, _
            FileFilter:="Excel-Compatible Files, *.xls;*.xlsx;*.xlsm;*.csv", _
            Title:=TitleString)

ActiveWorkbook.SaveAs Filename:=new2fn

'return to original default path
DirectorySetPath (SaveDriveDir)

'PullAllRawData = Now()
MsgBox "Source data file has been successfully created and saved" 
 
OldShortFN = ExtractFileName(newFN)
OldPathN = Left(newFN, Len(newFN) - Len(OldShortFN))
Debug.Print OldPathN & OldShortFN

Name newFN As OldPathN & OldShortFN

End Sub
0
Utf
5/27/2010 10:04:32 PM
excel.programming 6508 articles. 2 followers. Follow

6 Replies
680 Views

Similar Articles

[PageSpeed] 59

Additional note: I appended ".xls" to the filename in the code , but testing 
still results in corrupt output file(s).

"ker_01" wrote:

> I have code (below) that opens and processes a separate raw data file, 
> segregates data into several different worksheets in that data file, then 
> saves the file.
> 
> I just found out that my code appears to corrupt the output files, which 
> then cannot be opened in Excel2003, but can be opened in Excel2007. I can 
> open *other* Excel files without a problem, but files created with this sub 
> are corrupt. I suspect that it has to do with how the file is saved, so I'm 
> posting just that snippet first, then the full code underneath. Should I be 
> more restrictive in the Filefilter parameter? Or do I need to forceably add 
> the ".xls" extension even though the save dialogue shows it to be saving as 
> an XLS file? The file does save with the xls extension, and looks like an XL 
> file in windows explorer (opens in Excel2003 when double clicked, but then 
> throws an 'unrecognizable format' error, and shows a worksheet filled with 
> ASCII characters).
> 
> Any advice or suggestions would be greatly appreciated.
> 
> 
> Snippet:
> 
> DirectorySetPath ("\\wsak1\Perf\Score\Ops\Public\CurrentData")
> new2fn = Application.GetSaveAsFilename( _
>             InitialFileName:="2010 USA Ops Salary Increases - " & 
> PasteMonthNum & " " & StrMonth, _
>             FileFilter:="Excel-Compatible Files, *.xls;*.xlsx;*.xlsm;*.csv", _
>             Title:=TitleString)
> 
> ActiveWorkbook.SaveAs Filename:=new2fn
> 
> 
> 
> Full code:
> 
> Sub MakeReferenceWkbk()
> 
> 'default start path, editable by user from the filepicker dialogue
> PathOnly = "\\wsak1\Perf\Score\Ops\People\Increases"
> 
> Dim I As Integer
> Dim owb As Workbook 'original/main
> Dim twb As Workbook 'temp/data file
> Dim ows As Worksheet
> Dim tws As Worksheet
> 
> Set owb = ActiveWorkbook
> Set ows = ActiveWorkbook.ActiveSheet
> 
> Dim SaveDriveDir As String
> 'save default path
> SaveDriveDir = CurDir
> 
> TitleString = "Please select the Raw data file"
> 
>     'change to new path
>     DirectorySetPath (PathOnly)
>     
>     'get the file
>     newFN = Application.GetOpenFilename(FileFilter:="Excel-Compatible Files, 
> *.xls;*.xlsx;*.xlsm;*.csv", Title:=TitleString)
>     If newFN = False Then
>         ' They pressed Cancel
>         MsgBox "Stopping because you did not select a file"
>         'return to original default path
>         DirectorySetPath (SaveDriveDir)
>         Exit Sub
>     Else
>         MyFullFilePath = newFN
>     End If
> 
> Application.StatusBar = "Opening File " & MyFullFilePath
>     
> 'Open source workbook
> Application.DisplayAlerts = False
> Set twb = Workbooks.Open(Filename:=MyFullFilePath, UpdateLinks:=0, 
> ReadOnly:=True)
> Application.DisplayAlerts = True
> twb.Activate
> twb.Sheets(1).Activate
> 
> 'update the file
>     For I = 1 To 6
>         ActiveWorkbook.Sheets.Add
>     Next
>     
>     shtNameArr = Array("NWR", "NAR", "SWR", "SAR", "WWR")
>     
>     For I = 1 To 5
>         ActiveWorkbook.Sheets(7).Select
>         ActiveWorkbook.Sheets(7).Cells.Select
>         Selection.AutoFilter
>         Selection.AutoFilter Field:=9, Criteria1:=shtNameArr(I)
>         ActiveWorkbook.Sheets(7).Cells.Select
>         Selection.Copy
>         ActiveWorkbook.Sheets(I).Select
>         ActiveSheet.Paste
>         LRow = lastRow(Sheets(I))
>         ActiveSheet.Name = shtNameArr(I)
>         ActiveSheet.Cells.Select
>         ActiveSheet.Cells.EntireColumn.AutoFit
>         ActiveSheet.Range("V1").Select
>         Application.CutCopyMode = False
>         ActiveCell.Value = "Days Late"
>         ActiveSheet.Range("V2").Select
>         ActiveCell.Formula = "=IF(M2>L2,M2-L2,"""")"
>         ActiveSheet.Range("V2").Select
>         Selection.AutoFill Destination:=Range("V2:V" & LRow)
>         ActiveSheet.Range("A1").Select
>     Next
>     
>         ActiveWorkbook.Sheets(7).Select
>         ActiveWorkbook.Sheets(7).Cells.Select
>         Selection.AutoFilter
>         Selection.AutoFilter Field:=9, Criteria1:="STTC"
>         Selection.AutoFilter Field:=18, Criteria1:="=03", Operator:=xlOr, 
> Criteria2:="=04"
>         ActiveWorkbook.Sheets(7).Cells.Select
>         Selection.Copy
>         ActiveWorkbook.Sheets(6).Select
>         ActiveSheet.Paste
>         LRow = lastRow(ActiveWorkbook.Sheets(6))
>         ActiveSheet.Name = "STTC"
>         ActiveSheet.Cells.Select
>         ActiveSheet.Cells.EntireColumn.AutoFit
>         ActiveSheet.Range("V1").Select
>         Application.CutCopyMode = False
>         ActiveCell.Value = "Days Late"
>         ActiveSheet.Range("V2").Select
>         ActiveCell.Formula = "=IF(M2>L2,M2-L2,"""")"
>         ActiveSheet.Range("V2").Select
>         Selection.AutoFill Destination:=Range("V2:V" & LRow)
>         ActiveSheet.Range("A1").Select
>     
>     ActiveWorkbook.Sheets(1).Activate
>     Application.DisplayAlerts = False
>     ActiveWorkbook.Sheets(7).Delete
>     Application.DisplayAlerts = True
>   
> 'Select/copy a single cell to avoid clipboard warnings
> ActiveSheet.Range("A1").Copy
>     
> ''close the workbook to get it out of the way
> 'Application.DisplayAlerts = False 'just in case the clipboard trick doesn't 
> work
> 'twb.Close SaveChanges:=False
> 'Application.DisplayAlerts = True
> 
> Application.StatusBar = False
> 
> sDate = Year(Now()) & Format(Month(Now()), "00") & Format(Day(Now()), "00")
> ShortFileName = ExtractFileName(MyFullFilePath)
> 
> 'get the month "name" for the data set being saved, to put it in the filename
> PasteMonths = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
> PasteMonths2 = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", 
> "Sep", "Oct", "Nov", "Dec")
> PasteMonthNum = CInt(InputBox("Enter the month number represented by this 
> data (e.g. 1 for Jan, 2 for Feb, etc)", "Enter Data Month"))
> If IsError(Application.Match(PasteMonthNum, PasteMonths, False)) Then
>     MsgBox "Unable to recognize a date from 1 to 12." & Chr(13) & Chr(13) & 
> "Output file not saved; please run again to finish processing", , "Month 
> Number Error"
>     Exit Sub
> Else
>     StrMonth = PasteMonths2(PasteMonthNum)
> End If
> 
> DirectorySetPath ("\\wsak1\Perf\Score\Ops\Public\CurrentData")
> 'GetSaveAsFilename
> new2fn = Application.GetSaveAsFilename( _
>             InitialFileName:="2010 Ops Increases - " & PasteMonthNum & " " & 
> StrMonth, _
>             FileFilter:="Excel-Compatible Files, *.xls;*.xlsx;*.xlsm;*.csv", _
>             Title:=TitleString)
> 
> ActiveWorkbook.SaveAs Filename:=new2fn
> 
> 'return to original default path
> DirectorySetPath (SaveDriveDir)
> 
> 'PullAllRawData = Now()
> MsgBox "Source data file has been successfully created and saved" 
>  
> OldShortFN = ExtractFileName(newFN)
> OldPathN = Left(newFN, Len(newFN) - Len(OldShortFN))
> Debug.Print OldPathN & OldShortFN
> 
> Name newFN As OldPathN & OldShortFN
> 
> End Sub
0
Utf
5/27/2010 9:10:08 PM
Final note:

When I interrupt the code right after the save, and go into that file and 
save it manually, the autosaved version is .xls and is still corrupt. When I 
click manually within the file to save it, it defaults to Excel 2007 
filetype, with the corresponding .xlsx extention. 

Does the filesave dialogue automatically append the first filter in the 
dialogue? That would fit what I'm seeing as output. The original data file is 
stored as xlsx, so that would make sense as why the file is saving with an 
internal 2007 format. I'm just surprised that it isn't an automatic 
conversion (I'm inclined to think of this as a bug), and I'm not sure how to 
force it to save not just with the xls extension, but in fact in XL2003 
format.

Thanks,
Keith

"ker_01" wrote:

> Additional note: I appended ".xls" to the filename in the code , but testing 
> still results in corrupt output file(s).
> 
> "ker_01" wrote:
> 
> > I have code (below) that opens and processes a separate raw data file, 
> > segregates data into several different worksheets in that data file, then 
> > saves the file.
> > 
> > I just found out that my code appears to corrupt the output files, which 
> > then cannot be opened in Excel2003, but can be opened in Excel2007. I can 
> > open *other* Excel files without a problem, but files created with this sub 
> > are corrupt. I suspect that it has to do with how the file is saved, so I'm 
> > posting just that snippet first, then the full code underneath. Should I be 
> > more restrictive in the Filefilter parameter? Or do I need to forceably add 
> > the ".xls" extension even though the save dialogue shows it to be saving as 
> > an XLS file? The file does save with the xls extension, and looks like an XL 
> > file in windows explorer (opens in Excel2003 when double clicked, but then 
> > throws an 'unrecognizable format' error, and shows a worksheet filled with 
> > ASCII characters).
> > 
> > Any advice or suggestions would be greatly appreciated.
> > 
> > 
> > Snippet:
> > 
> > DirectorySetPath ("\\wsak1\Perf\Score\Ops\Public\CurrentData")
> > new2fn = Application.GetSaveAsFilename( _
> >             InitialFileName:="2010 USA Ops Salary Increases - " & 
> > PasteMonthNum & " " & StrMonth, _
> >             FileFilter:="Excel-Compatible Files, *.xls;*.xlsx;*.xlsm;*.csv", _
> >             Title:=TitleString)
> > 
> > ActiveWorkbook.SaveAs Filename:=new2fn
> > 
> > 
> > 
> > Full code:
> > 
> > Sub MakeReferenceWkbk()
> > 
> > 'default start path, editable by user from the filepicker dialogue
> > PathOnly = "\\wsak1\Perf\Score\Ops\People\Increases"
> > 
> > Dim I As Integer
> > Dim owb As Workbook 'original/main
> > Dim twb As Workbook 'temp/data file
> > Dim ows As Worksheet
> > Dim tws As Worksheet
> > 
> > Set owb = ActiveWorkbook
> > Set ows = ActiveWorkbook.ActiveSheet
> > 
> > Dim SaveDriveDir As String
> > 'save default path
> > SaveDriveDir = CurDir
> > 
> > TitleString = "Please select the Raw data file"
> > 
> >     'change to new path
> >     DirectorySetPath (PathOnly)
> >     
> >     'get the file
> >     newFN = Application.GetOpenFilename(FileFilter:="Excel-Compatible Files, 
> > *.xls;*.xlsx;*.xlsm;*.csv", Title:=TitleString)
> >     If newFN = False Then
> >         ' They pressed Cancel
> >         MsgBox "Stopping because you did not select a file"
> >         'return to original default path
> >         DirectorySetPath (SaveDriveDir)
> >         Exit Sub
> >     Else
> >         MyFullFilePath = newFN
> >     End If
> > 
> > Application.StatusBar = "Opening File " & MyFullFilePath
> >     
> > 'Open source workbook
> > Application.DisplayAlerts = False
> > Set twb = Workbooks.Open(Filename:=MyFullFilePath, UpdateLinks:=0, 
> > ReadOnly:=True)
> > Application.DisplayAlerts = True
> > twb.Activate
> > twb.Sheets(1).Activate
> > 
> > 'update the file
> >     For I = 1 To 6
> >         ActiveWorkbook.Sheets.Add
> >     Next
> >     
> >     shtNameArr = Array("NWR", "NAR", "SWR", "SAR", "WWR")
> >     
> >     For I = 1 To 5
> >         ActiveWorkbook.Sheets(7).Select
> >         ActiveWorkbook.Sheets(7).Cells.Select
> >         Selection.AutoFilter
> >         Selection.AutoFilter Field:=9, Criteria1:=shtNameArr(I)
> >         ActiveWorkbook.Sheets(7).Cells.Select
> >         Selection.Copy
> >         ActiveWorkbook.Sheets(I).Select
> >         ActiveSheet.Paste
> >         LRow = lastRow(Sheets(I))
> >         ActiveSheet.Name = shtNameArr(I)
> >         ActiveSheet.Cells.Select
> >         ActiveSheet.Cells.EntireColumn.AutoFit
> >         ActiveSheet.Range("V1").Select
> >         Application.CutCopyMode = False
> >         ActiveCell.Value = "Days Late"
> >         ActiveSheet.Range("V2").Select
> >         ActiveCell.Formula = "=IF(M2>L2,M2-L2,"""")"
> >         ActiveSheet.Range("V2").Select
> >         Selection.AutoFill Destination:=Range("V2:V" & LRow)
> >         ActiveSheet.Range("A1").Select
> >     Next
> >     
> >         ActiveWorkbook.Sheets(7).Select
> >         ActiveWorkbook.Sheets(7).Cells.Select
> >         Selection.AutoFilter
> >         Selection.AutoFilter Field:=9, Criteria1:="STTC"
> >         Selection.AutoFilter Field:=18, Criteria1:="=03", Operator:=xlOr, 
> > Criteria2:="=04"
> >         ActiveWorkbook.Sheets(7).Cells.Select
> >         Selection.Copy
> >         ActiveWorkbook.Sheets(6).Select
> >         ActiveSheet.Paste
> >         LRow = lastRow(ActiveWorkbook.Sheets(6))
> >         ActiveSheet.Name = "STTC"
> >         ActiveSheet.Cells.Select
> >         ActiveSheet.Cells.EntireColumn.AutoFit
> >         ActiveSheet.Range("V1").Select
> >         Application.CutCopyMode = False
> >         ActiveCell.Value = "Days Late"
> >         ActiveSheet.Range("V2").Select
> >         ActiveCell.Formula = "=IF(M2>L2,M2-L2,"""")"
> >         ActiveSheet.Range("V2").Select
> >         Selection.AutoFill Destination:=Range("V2:V" & LRow)
> >         ActiveSheet.Range("A1").Select
> >     
> >     ActiveWorkbook.Sheets(1).Activate
> >     Application.DisplayAlerts = False
> >     ActiveWorkbook.Sheets(7).Delete
> >     Application.DisplayAlerts = True
> >   
> > 'Select/copy a single cell to avoid clipboard warnings
> > ActiveSheet.Range("A1").Copy
> >     
> > ''close the workbook to get it out of the way
> > 'Application.DisplayAlerts = False 'just in case the clipboard trick doesn't 
> > work
> > 'twb.Close SaveChanges:=False
> > 'Application.DisplayAlerts = True
> > 
> > Application.StatusBar = False
> > 
> > sDate = Year(Now()) & Format(Month(Now()), "00") & Format(Day(Now()), "00")
> > ShortFileName = ExtractFileName(MyFullFilePath)
> > 
> > 'get the month "name" for the data set being saved, to put it in the filename
> > PasteMonths = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
> > PasteMonths2 = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", 
> > "Sep", "Oct", "Nov", "Dec")
> > PasteMonthNum = CInt(InputBox("Enter the month number represented by this 
> > data (e.g. 1 for Jan, 2 for Feb, etc)", "Enter Data Month"))
> > If IsError(Application.Match(PasteMonthNum, PasteMonths, False)) Then
> >     MsgBox "Unable to recognize a date from 1 to 12." & Chr(13) & Chr(13) & 
> > "Output file not saved; please run again to finish processing", , "Month 
> > Number Error"
> >     Exit Sub
> > Else
> >     StrMonth = PasteMonths2(PasteMonthNum)
> > End If
> > 
> > DirectorySetPath ("\\wsak1\Perf\Score\Ops\Public\CurrentData")
> > 'GetSaveAsFilename
> > new2fn = Application.GetSaveAsFilename( _
> >             InitialFileName:="2010 Ops Increases - " & PasteMonthNum & " " & 
> > StrMonth, _
> >             FileFilter:="Excel-Compatible Files, *.xls;*.xlsx;*.xlsm;*.csv", _
> >             Title:=TitleString)
> > 
> > ActiveWorkbook.SaveAs Filename:=new2fn
> > 
> > 'return to original default path
> > DirectorySetPath (SaveDriveDir)
> > 
> > 'PullAllRawData = Now()
> > MsgBox "Source data file has been successfully created and saved" 
> >  
> > OldShortFN = ExtractFileName(newFN)
> > OldPathN = Left(newFN, Len(newFN) - Len(OldShortFN))
> > Debug.Print OldPathN & OldShortFN
> > 
> > Name newFN As OldPathN & OldShortFN
> > 
> > End Sub
0
Utf
5/27/2010 9:31:44 PM
For future reference; the files saved "uncorrupted" if I forced the file 
extension to .xlsx (2007 format). Unfortunately, that adds time to convert 
the file while saving, and convert each time the file is re-opened.

I'd still describe this as a bug; if Excel is going to save in 2007 format, 
it should automatically default to saving with an xlsx extension instead of 
xls

My original question remains; how do I force Excel to save the file in 
actual 2003 file format, when the original file that was opened (via VBA) was 
2007 format?

Thanks,
Keith

"ker_01" wrote:

> Final note:
> 
> When I interrupt the code right after the save, and go into that file and 
> save it manually, the autosaved version is .xls and is still corrupt. When I 
> click manually within the file to save it, it defaults to Excel 2007 
> filetype, with the corresponding .xlsx extention. 
> 
> Does the filesave dialogue automatically append the first filter in the 
> dialogue? That would fit what I'm seeing as output. The original data file is 
> stored as xlsx, so that would make sense as why the file is saving with an 
> internal 2007 format. I'm just surprised that it isn't an automatic 
> conversion (I'm inclined to think of this as a bug), and I'm not sure how to 
> force it to save not just with the xls extension, but in fact in XL2003 
> format.
> 
> Thanks,
> Keith
> 
> "ker_01" wrote:
> 
> > Additional note: I appended ".xls" to the filename in the code , but testing 
> > still results in corrupt output file(s).
> > 
> > "ker_01" wrote:
> > 
> > > I have code (below) that opens and processes a separate raw data file, 
> > > segregates data into several different worksheets in that data file, then 
> > > saves the file.
> > > 
> > > I just found out that my code appears to corrupt the output files, which 
> > > then cannot be opened in Excel2003, but can be opened in Excel2007. I can 
> > > open *other* Excel files without a problem, but files created with this sub 
> > > are corrupt. I suspect that it has to do with how the file is saved, so I'm 
> > > posting just that snippet first, then the full code underneath. Should I be 
> > > more restrictive in the Filefilter parameter? Or do I need to forceably add 
> > > the ".xls" extension even though the save dialogue shows it to be saving as 
> > > an XLS file? The file does save with the xls extension, and looks like an XL 
> > > file in windows explorer (opens in Excel2003 when double clicked, but then 
> > > throws an 'unrecognizable format' error, and shows a worksheet filled with 
> > > ASCII characters).
> > > 
> > > Any advice or suggestions would be greatly appreciated.
> > > 
> > > 
> > > Snippet:
> > > 
> > > DirectorySetPath ("\\wsak1\Perf\Score\Ops\Public\CurrentData")
> > > new2fn = Application.GetSaveAsFilename( _
> > >             InitialFileName:="2010 USA Ops Salary Increases - " & 
> > > PasteMonthNum & " " & StrMonth, _
> > >             FileFilter:="Excel-Compatible Files, *.xls;*.xlsx;*.xlsm;*.csv", _
> > >             Title:=TitleString)
> > > 
> > > ActiveWorkbook.SaveAs Filename:=new2fn
> > > 
> > > 
> > > 
> > > Full code:
> > > 
> > > Sub MakeReferenceWkbk()
> > > 
> > > 'default start path, editable by user from the filepicker dialogue
> > > PathOnly = "\\wsak1\Perf\Score\Ops\People\Increases"
> > > 
> > > Dim I As Integer
> > > Dim owb As Workbook 'original/main
> > > Dim twb As Workbook 'temp/data file
> > > Dim ows As Worksheet
> > > Dim tws As Worksheet
> > > 
> > > Set owb = ActiveWorkbook
> > > Set ows = ActiveWorkbook.ActiveSheet
> > > 
> > > Dim SaveDriveDir As String
> > > 'save default path
> > > SaveDriveDir = CurDir
> > > 
> > > TitleString = "Please select the Raw data file"
> > > 
> > >     'change to new path
> > >     DirectorySetPath (PathOnly)
> > >     
> > >     'get the file
> > >     newFN = Application.GetOpenFilename(FileFilter:="Excel-Compatible Files, 
> > > *.xls;*.xlsx;*.xlsm;*.csv", Title:=TitleString)
> > >     If newFN = False Then
> > >         ' They pressed Cancel
> > >         MsgBox "Stopping because you did not select a file"
> > >         'return to original default path
> > >         DirectorySetPath (SaveDriveDir)
> > >         Exit Sub
> > >     Else
> > >         MyFullFilePath = newFN
> > >     End If
> > > 
> > > Application.StatusBar = "Opening File " & MyFullFilePath
> > >     
> > > 'Open source workbook
> > > Application.DisplayAlerts = False
> > > Set twb = Workbooks.Open(Filename:=MyFullFilePath, UpdateLinks:=0, 
> > > ReadOnly:=True)
> > > Application.DisplayAlerts = True
> > > twb.Activate
> > > twb.Sheets(1).Activate
> > > 
> > > 'update the file
> > >     For I = 1 To 6
> > >         ActiveWorkbook.Sheets.Add
> > >     Next
> > >     
> > >     shtNameArr = Array("NWR", "NAR", "SWR", "SAR", "WWR")
> > >     
> > >     For I = 1 To 5
> > >         ActiveWorkbook.Sheets(7).Select
> > >         ActiveWorkbook.Sheets(7).Cells.Select
> > >         Selection.AutoFilter
> > >         Selection.AutoFilter Field:=9, Criteria1:=shtNameArr(I)
> > >         ActiveWorkbook.Sheets(7).Cells.Select
> > >         Selection.Copy
> > >         ActiveWorkbook.Sheets(I).Select
> > >         ActiveSheet.Paste
> > >         LRow = lastRow(Sheets(I))
> > >         ActiveSheet.Name = shtNameArr(I)
> > >         ActiveSheet.Cells.Select
> > >         ActiveSheet.Cells.EntireColumn.AutoFit
> > >         ActiveSheet.Range("V1").Select
> > >         Application.CutCopyMode = False
> > >         ActiveCell.Value = "Days Late"
> > >         ActiveSheet.Range("V2").Select
> > >         ActiveCell.Formula = "=IF(M2>L2,M2-L2,"""")"
> > >         ActiveSheet.Range("V2").Select
> > >         Selection.AutoFill Destination:=Range("V2:V" & LRow)
> > >         ActiveSheet.Range("A1").Select
> > >     Next
> > >     
> > >         ActiveWorkbook.Sheets(7).Select
> > >         ActiveWorkbook.Sheets(7).Cells.Select
> > >         Selection.AutoFilter
> > >         Selection.AutoFilter Field:=9, Criteria1:="STTC"
> > >         Selection.AutoFilter Field:=18, Criteria1:="=03", Operator:=xlOr, 
> > > Criteria2:="=04"
> > >         ActiveWorkbook.Sheets(7).Cells.Select
> > >         Selection.Copy
> > >         ActiveWorkbook.Sheets(6).Select
> > >         ActiveSheet.Paste
> > >         LRow = lastRow(ActiveWorkbook.Sheets(6))
> > >         ActiveSheet.Name = "STTC"
> > >         ActiveSheet.Cells.Select
> > >         ActiveSheet.Cells.EntireColumn.AutoFit
> > >         ActiveSheet.Range("V1").Select
> > >         Application.CutCopyMode = False
> > >         ActiveCell.Value = "Days Late"
> > >         ActiveSheet.Range("V2").Select
> > >         ActiveCell.Formula = "=IF(M2>L2,M2-L2,"""")"
> > >         ActiveSheet.Range("V2").Select
> > >         Selection.AutoFill Destination:=Range("V2:V" & LRow)
> > >         ActiveSheet.Range("A1").Select
> > >     
> > >     ActiveWorkbook.Sheets(1).Activate
> > >     Application.DisplayAlerts = False
> > >     ActiveWorkbook.Sheets(7).Delete
> > >     Application.DisplayAlerts = True
> > >   
> > > 'Select/copy a single cell to avoid clipboard warnings
> > > ActiveSheet.Range("A1").Copy
> > >     
> > > ''close the workbook to get it out of the way
> > > 'Application.DisplayAlerts = False 'just in case the clipboard trick doesn't 
> > > work
> > > 'twb.Close SaveChanges:=False
> > > 'Application.DisplayAlerts = True
> > > 
> > > Application.StatusBar = False
> > > 
> > > sDate = Year(Now()) & Format(Month(Now()), "00") & Format(Day(Now()), "00")
> > > ShortFileName = ExtractFileName(MyFullFilePath)
> > > 
> > > 'get the month "name" for the data set being saved, to put it in the filename
> > > PasteMonths = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
> > > PasteMonths2 = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", 
> > > "Sep", "Oct", "Nov", "Dec")
> > > PasteMonthNum = CInt(InputBox("Enter the month number represented by this 
> > > data (e.g. 1 for Jan, 2 for Feb, etc)", "Enter Data Month"))
> > > If IsError(Application.Match(PasteMonthNum, PasteMonths, False)) Then
> > >     MsgBox "Unable to recognize a date from 1 to 12." & Chr(13) & Chr(13) & 
> > > "Output file not saved; please run again to finish processing", , "Month 
> > > Number Error"
> > >     Exit Sub
> > > Else
> > >     StrMonth = PasteMonths2(PasteMonthNum)
> > > End If
> > > 
> > > DirectorySetPath ("\\wsak1\Perf\Score\Ops\Public\CurrentData")
> > > 'GetSaveAsFilename
> > > new2fn = Application.GetSaveAsFilename( _
> > >             InitialFileName:="2010 Ops Increases - " & PasteMonthNum & " " & 
> > > StrMonth, _
> > >             FileFilter:="Excel-Compatible Files, *.xls;*.xlsx;*.xlsm;*.csv", _
> > >             Title:=TitleString)
> > > 
> > > ActiveWorkbook.SaveAs Filename:=new2fn
> > > 
> > > 'return to original default path
> > > DirectorySetPath (SaveDriveDir)
> > > 
> > > 'PullAllRawData = Now()
> > > MsgBox "Source data file has been successfully created and saved" 
> > >  
> > > OldShortFN = ExtractFileName(newFN)
> > > OldPathN = Left(newFN, Len(newFN) - Len(OldShortFN))
> > > Debug.Print OldPathN & OldShortFN
> > > 
> > > Name newFN As OldPathN & OldShortFN
> > > 
> > > End Sub
0
Utf
5/27/2010 9:46:00 PM
Your code specifies a filename but not a file format when saving the 
workbooks. Default format is xlsx, but if you use a different file 
extension, Excel chokes when it tries opening the misnamed file.

Use this to save as Excel 97-2003 format:

ActiveWorkbook.SaveAs Filename:=new2fn, FileFormat:=xlExcel8

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/


On 5/27/2010 6:04 PM, ker_01 wrote:
> I have code (below) that opens and processes a separate raw data file,
> segregates data into several different worksheets in that data file, then
> saves the file.
>
> I just found out that my code appears to corrupt the output files, which
> then cannot be opened in Excel2003, but can be opened in Excel2007. I can
> open *other* Excel files without a problem, but files created with this sub
> are corrupt. I suspect that it has to do with how the file is saved, so I'm
> posting just that snippet first, then the full code underneath. Should I be
> more restrictive in the Filefilter parameter? Or do I need to forceably add
> the ".xls" extension even though the save dialogue shows it to be saving as
> an XLS file? The file does save with the xls extension, and looks like an XL
> file in windows explorer (opens in Excel2003 when double clicked, but then
> throws an 'unrecognizable format' error, and shows a worksheet filled with
> ASCII characters).
>
> Any advice or suggestions would be greatly appreciated.
>
>
> Snippet:
>
> DirectorySetPath ("\\wsak1\Perf\Score\Ops\Public\CurrentData")
> new2fn = Application.GetSaveAsFilename( _
>              InitialFileName:="2010 USA Ops Salary Increases - "&
> PasteMonthNum&  " "&  StrMonth, _
>              FileFilter:="Excel-Compatible Files, *.xls;*.xlsx;*.xlsm;*.csv", _
>              Title:=TitleString)
>
> ActiveWorkbook.SaveAs Filename:=new2fn
>
>
>
> Full code:
>
> Sub MakeReferenceWkbk()
>
> 'default start path, editable by user from the filepicker dialogue
> PathOnly = "\\wsak1\Perf\Score\Ops\People\Increases"
>
> Dim I As Integer
> Dim owb As Workbook 'original/main
> Dim twb As Workbook 'temp/data file
> Dim ows As Worksheet
> Dim tws As Worksheet
>
> Set owb = ActiveWorkbook
> Set ows = ActiveWorkbook.ActiveSheet
>
> Dim SaveDriveDir As String
> 'save default path
> SaveDriveDir = CurDir
>
> TitleString = "Please select the Raw data file"
>
>      'change to new path
>      DirectorySetPath (PathOnly)
>
>      'get the file
>      newFN = Application.GetOpenFilename(FileFilter:="Excel-Compatible Files,
> *.xls;*.xlsx;*.xlsm;*.csv", Title:=TitleString)
>      If newFN = False Then
>          ' They pressed Cancel
>          MsgBox "Stopping because you did not select a file"
>          'return to original default path
>          DirectorySetPath (SaveDriveDir)
>          Exit Sub
>      Else
>          MyFullFilePath = newFN
>      End If
>
> Application.StatusBar = "Opening File "&  MyFullFilePath
>
> 'Open source workbook
> Application.DisplayAlerts = False
> Set twb = Workbooks.Open(Filename:=MyFullFilePath, UpdateLinks:=0,
> ReadOnly:=True)
> Application.DisplayAlerts = True
> twb.Activate
> twb.Sheets(1).Activate
>
> 'update the file
>      For I = 1 To 6
>          ActiveWorkbook.Sheets.Add
>      Next
>
>      shtNameArr = Array("NWR", "NAR", "SWR", "SAR", "WWR")
>
>      For I = 1 To 5
>          ActiveWorkbook.Sheets(7).Select
>          ActiveWorkbook.Sheets(7).Cells.Select
>          Selection.AutoFilter
>          Selection.AutoFilter Field:=9, Criteria1:=shtNameArr(I)
>          ActiveWorkbook.Sheets(7).Cells.Select
>          Selection.Copy
>          ActiveWorkbook.Sheets(I).Select
>          ActiveSheet.Paste
>          LRow = lastRow(Sheets(I))
>          ActiveSheet.Name = shtNameArr(I)
>          ActiveSheet.Cells.Select
>          ActiveSheet.Cells.EntireColumn.AutoFit
>          ActiveSheet.Range("V1").Select
>          Application.CutCopyMode = False
>          ActiveCell.Value = "Days Late"
>          ActiveSheet.Range("V2").Select
>          ActiveCell.Formula = "=IF(M2>L2,M2-L2,"""")"
>          ActiveSheet.Range("V2").Select
>          Selection.AutoFill Destination:=Range("V2:V"&  LRow)
>          ActiveSheet.Range("A1").Select
>      Next
>
>          ActiveWorkbook.Sheets(7).Select
>          ActiveWorkbook.Sheets(7).Cells.Select
>          Selection.AutoFilter
>          Selection.AutoFilter Field:=9, Criteria1:="STTC"
>          Selection.AutoFilter Field:=18, Criteria1:="=03", Operator:=xlOr,
> Criteria2:="=04"
>          ActiveWorkbook.Sheets(7).Cells.Select
>          Selection.Copy
>          ActiveWorkbook.Sheets(6).Select
>          ActiveSheet.Paste
>          LRow = lastRow(ActiveWorkbook.Sheets(6))
>          ActiveSheet.Name = "STTC"
>          ActiveSheet.Cells.Select
>          ActiveSheet.Cells.EntireColumn.AutoFit
>          ActiveSheet.Range("V1").Select
>          Application.CutCopyMode = False
>          ActiveCell.Value = "Days Late"
>          ActiveSheet.Range("V2").Select
>          ActiveCell.Formula = "=IF(M2>L2,M2-L2,"""")"
>          ActiveSheet.Range("V2").Select
>          Selection.AutoFill Destination:=Range("V2:V"&  LRow)
>          ActiveSheet.Range("A1").Select
>
>      ActiveWorkbook.Sheets(1).Activate
>      Application.DisplayAlerts = False
>      ActiveWorkbook.Sheets(7).Delete
>      Application.DisplayAlerts = True
>
> 'Select/copy a single cell to avoid clipboard warnings
> ActiveSheet.Range("A1").Copy
>
> ''close the workbook to get it out of the way
> 'Application.DisplayAlerts = False 'just in case the clipboard trick doesn't
> work
> 'twb.Close SaveChanges:=False
> 'Application.DisplayAlerts = True
>
> Application.StatusBar = False
>
> sDate = Year(Now())&  Format(Month(Now()), "00")&  Format(Day(Now()), "00")
> ShortFileName = ExtractFileName(MyFullFilePath)
>
> 'get the month "name" for the data set being saved, to put it in the filename
> PasteMonths = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
> PasteMonths2 = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug",
> "Sep", "Oct", "Nov", "Dec")
> PasteMonthNum = CInt(InputBox("Enter the month number represented by this
> data (e.g. 1 for Jan, 2 for Feb, etc)", "Enter Data Month"))
> If IsError(Application.Match(PasteMonthNum, PasteMonths, False)) Then
>      MsgBox "Unable to recognize a date from 1 to 12."&  Chr(13)&  Chr(13)&
> "Output file not saved; please run again to finish processing", , "Month
> Number Error"
>      Exit Sub
> Else
>      StrMonth = PasteMonths2(PasteMonthNum)
> End If
>
> DirectorySetPath ("\\wsak1\Perf\Score\Ops\Public\CurrentData")
> 'GetSaveAsFilename
> new2fn = Application.GetSaveAsFilename( _
>              InitialFileName:="2010 Ops Increases - "&  PasteMonthNum&  " "&
> StrMonth, _
>              FileFilter:="Excel-Compatible Files, *.xls;*.xlsx;*.xlsm;*.csv", _
>              Title:=TitleString)
>
> ActiveWorkbook.SaveAs Filename:=new2fn
>
> 'return to original default path
> DirectorySetPath (SaveDriveDir)
>
> 'PullAllRawData = Now()
> MsgBox "Source data file has been successfully created and saved"
>
> OldShortFN = ExtractFileName(newFN)
> OldPathN = Left(newFN, Len(newFN) - Len(OldShortFN))
> Debug.Print OldPathN&  OldShortFN
>
> Name newFN As OldPathN&  OldShortFN
>
> End Sub
0
Jon
5/28/2010 6:28:29 PM
Awesome, thank you Jon!

"Jon Peltier" wrote:

> Your code specifies a filename but not a file format when saving the 
> workbooks. Default format is xlsx, but if you use a different file 
> extension, Excel chokes when it tries opening the misnamed file.
> 
> Use this to save as Excel 97-2003 format:
> 
> ActiveWorkbook.SaveAs Filename:=new2fn, FileFormat:=xlExcel8
> 
> - Jon
> -------
> Jon Peltier
> Peltier Technical Services, Inc.
> http://peltiertech.com/
> 
> 
> On 5/27/2010 6:04 PM, ker_01 wrote:
> > I have code (below) that opens and processes a separate raw data file,
> > segregates data into several different worksheets in that data file, then
> > saves the file.
> >
> > I just found out that my code appears to corrupt the output files, which
> > then cannot be opened in Excel2003, but can be opened in Excel2007. I can
> > open *other* Excel files without a problem, but files created with this sub
> > are corrupt. I suspect that it has to do with how the file is saved, so I'm
> > posting just that snippet first, then the full code underneath. Should I be
> > more restrictive in the Filefilter parameter? Or do I need to forceably add
> > the ".xls" extension even though the save dialogue shows it to be saving as
> > an XLS file? The file does save with the xls extension, and looks like an XL
> > file in windows explorer (opens in Excel2003 when double clicked, but then
> > throws an 'unrecognizable format' error, and shows a worksheet filled with
> > ASCII characters).
> >
> > Any advice or suggestions would be greatly appreciated.
> >
> >
> > Snippet:
> >
> > DirectorySetPath ("\\wsak1\Perf\Score\Ops\Public\CurrentData")
> > new2fn = Application.GetSaveAsFilename( _
> >              InitialFileName:="2010 USA Ops Salary Increases - "&
> > PasteMonthNum&  " "&  StrMonth, _
> >              FileFilter:="Excel-Compatible Files, *.xls;*.xlsx;*.xlsm;*.csv", _
> >              Title:=TitleString)
> >
> > ActiveWorkbook.SaveAs Filename:=new2fn
> >
> >
> >
> > Full code:
> >
> > Sub MakeReferenceWkbk()
> >
> > 'default start path, editable by user from the filepicker dialogue
> > PathOnly = "\\wsak1\Perf\Score\Ops\People\Increases"
> >
> > Dim I As Integer
> > Dim owb As Workbook 'original/main
> > Dim twb As Workbook 'temp/data file
> > Dim ows As Worksheet
> > Dim tws As Worksheet
> >
> > Set owb = ActiveWorkbook
> > Set ows = ActiveWorkbook.ActiveSheet
> >
> > Dim SaveDriveDir As String
> > 'save default path
> > SaveDriveDir = CurDir
> >
> > TitleString = "Please select the Raw data file"
> >
> >      'change to new path
> >      DirectorySetPath (PathOnly)
> >
> >      'get the file
> >      newFN = Application.GetOpenFilename(FileFilter:="Excel-Compatible Files,
> > *.xls;*.xlsx;*.xlsm;*.csv", Title:=TitleString)
> >      If newFN = False Then
> >          ' They pressed Cancel
> >          MsgBox "Stopping because you did not select a file"
> >          'return to original default path
> >          DirectorySetPath (SaveDriveDir)
> >          Exit Sub
> >      Else
> >          MyFullFilePath = newFN
> >      End If
> >
> > Application.StatusBar = "Opening File "&  MyFullFilePath
> >
> > 'Open source workbook
> > Application.DisplayAlerts = False
> > Set twb = Workbooks.Open(Filename:=MyFullFilePath, UpdateLinks:=0,
> > ReadOnly:=True)
> > Application.DisplayAlerts = True
> > twb.Activate
> > twb.Sheets(1).Activate
> >
> > 'update the file
> >      For I = 1 To 6
> >          ActiveWorkbook.Sheets.Add
> >      Next
> >
> >      shtNameArr = Array("NWR", "NAR", "SWR", "SAR", "WWR")
> >
> >      For I = 1 To 5
> >          ActiveWorkbook.Sheets(7).Select
> >          ActiveWorkbook.Sheets(7).Cells.Select
> >          Selection.AutoFilter
> >          Selection.AutoFilter Field:=9, Criteria1:=shtNameArr(I)
> >          ActiveWorkbook.Sheets(7).Cells.Select
> >          Selection.Copy
> >          ActiveWorkbook.Sheets(I).Select
> >          ActiveSheet.Paste
> >          LRow = lastRow(Sheets(I))
> >          ActiveSheet.Name = shtNameArr(I)
> >          ActiveSheet.Cells.Select
> >          ActiveSheet.Cells.EntireColumn.AutoFit
> >          ActiveSheet.Range("V1").Select
> >          Application.CutCopyMode = False
> >          ActiveCell.Value = "Days Late"
> >          ActiveSheet.Range("V2").Select
> >          ActiveCell.Formula = "=IF(M2>L2,M2-L2,"""")"
> >          ActiveSheet.Range("V2").Select
> >          Selection.AutoFill Destination:=Range("V2:V"&  LRow)
> >          ActiveSheet.Range("A1").Select
> >      Next
> >
> >          ActiveWorkbook.Sheets(7).Select
> >          ActiveWorkbook.Sheets(7).Cells.Select
> >          Selection.AutoFilter
> >          Selection.AutoFilter Field:=9, Criteria1:="STTC"
> >          Selection.AutoFilter Field:=18, Criteria1:="=03", Operator:=xlOr,
> > Criteria2:="=04"
> >          ActiveWorkbook.Sheets(7).Cells.Select
> >          Selection.Copy
> >          ActiveWorkbook.Sheets(6).Select
> >          ActiveSheet.Paste
> >          LRow = lastRow(ActiveWorkbook.Sheets(6))
> >          ActiveSheet.Name = "STTC"
> >          ActiveSheet.Cells.Select
> >          ActiveSheet.Cells.EntireColumn.AutoFit
> >          ActiveSheet.Range("V1").Select
> >          Application.CutCopyMode = False
> >          ActiveCell.Value = "Days Late"
> >          ActiveSheet.Range("V2").Select
> >          ActiveCell.Formula = "=IF(M2>L2,M2-L2,"""")"
> >          ActiveSheet.Range("V2").Select
> >          Selection.AutoFill Destination:=Range("V2:V"&  LRow)
> >          ActiveSheet.Range("A1").Select
> >
> >      ActiveWorkbook.Sheets(1).Activate
> >      Application.DisplayAlerts = False
> >      ActiveWorkbook.Sheets(7).Delete
> >      Application.DisplayAlerts = True
> >
> > 'Select/copy a single cell to avoid clipboard warnings
> > ActiveSheet.Range("A1").Copy
> >
> > ''close the workbook to get it out of the way
> > 'Application.DisplayAlerts = False 'just in case the clipboard trick doesn't
> > work
> > 'twb.Close SaveChanges:=False
> > 'Application.DisplayAlerts = True
> >
> > Application.StatusBar = False
> >
> > sDate = Year(Now())&  Format(Month(Now()), "00")&  Format(Day(Now()), "00")
> > ShortFileName = ExtractFileName(MyFullFilePath)
> >
> > 'get the month "name" for the data set being saved, to put it in the filename
> > PasteMonths = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
> > PasteMonths2 = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug",
> > "Sep", "Oct", "Nov", "Dec")
> > PasteMonthNum = CInt(InputBox("Enter the month number represented by this
> > data (e.g. 1 for Jan, 2 for Feb, etc)", "Enter Data Month"))
> > If IsError(Application.Match(PasteMonthNum, PasteMonths, False)) Then
> >      MsgBox "Unable to recognize a date from 1 to 12."&  Chr(13)&  Chr(13)&
> > "Output file not saved; please run again to finish processing", , "Month
> > Number Error"
> >      Exit Sub
> > Else
> >      StrMonth = PasteMonths2(PasteMonthNum)
> > End If
> >
> > DirectorySetPath ("\\wsak1\Perf\Score\Ops\Public\CurrentData")
> > 'GetSaveAsFilename
> > new2fn = Application.GetSaveAsFilename( _
> >              InitialFileName:="2010 Ops Increases - "&  PasteMonthNum&  " "&
> > StrMonth, _
> >              FileFilter:="Excel-Compatible Files, *.xls;*.xlsx;*.xlsm;*.csv", _
> >              Title:=TitleString)
> >
> > ActiveWorkbook.SaveAs Filename:=new2fn
> >
> > 'return to original default path
> > DirectorySetPath (SaveDriveDir)
> >
> > 'PullAllRawData = Now()
> > MsgBox "Source data file has been successfully created and saved"
> >
> > OldShortFN = ExtractFileName(newFN)
> > OldPathN = Left(newFN, Len(newFN) - Len(OldShortFN))
> > Debug.Print OldPathN&  OldShortFN
> >
> > Name newFN As OldPathN&  OldShortFN
> >
> > End Sub
> .
> 
0
Utf
6/1/2010 3:30:02 PM
Can you tell that one tripped me up, costing several hours of unbillable 
work?

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/


On 6/1/2010 11:30 AM, ker_01 wrote:
> Awesome, thank you Jon!
>
> "Jon Peltier" wrote:
>
>> Your code specifies a filename but not a file format when saving the
>> workbooks. Default format is xlsx, but if you use a different file
>> extension, Excel chokes when it tries opening the misnamed file.
>>
>> Use this to save as Excel 97-2003 format:
>>
>> ActiveWorkbook.SaveAs Filename:=new2fn, FileFormat:=xlExcel8
>>
>> - Jon
>> -------
>> Jon Peltier
>> Peltier Technical Services, Inc.
>> http://peltiertech.com/
>>
>>
>> On 5/27/2010 6:04 PM, ker_01 wrote:
>>> I have code (below) that opens and processes a separate raw data file,
>>> segregates data into several different worksheets in that data file, then
>>> saves the file.
>>>
>>> I just found out that my code appears to corrupt the output files, which
>>> then cannot be opened in Excel2003, but can be opened in Excel2007. I can
>>> open *other* Excel files without a problem, but files created with this sub
>>> are corrupt. I suspect that it has to do with how the file is saved, so I'm
>>> posting just that snippet first, then the full code underneath. Should I be
>>> more restrictive in the Filefilter parameter? Or do I need to forceably add
>>> the ".xls" extension even though the save dialogue shows it to be saving as
>>> an XLS file? The file does save with the xls extension, and looks like an XL
>>> file in windows explorer (opens in Excel2003 when double clicked, but then
>>> throws an 'unrecognizable format' error, and shows a worksheet filled with
>>> ASCII characters).
>>>
>>> Any advice or suggestions would be greatly appreciated.
>>>
>>>
>>> Snippet:
>>>
>>> DirectorySetPath ("\\wsak1\Perf\Score\Ops\Public\CurrentData")
>>> new2fn = Application.GetSaveAsFilename( _
>>>               InitialFileName:="2010 USA Ops Salary Increases - "&
>>> PasteMonthNum&   ""&   StrMonth, _
>>>               FileFilter:="Excel-Compatible Files, *.xls;*.xlsx;*.xlsm;*.csv", _
>>>               Title:=TitleString)
>>>
>>> ActiveWorkbook.SaveAs Filename:=new2fn
>>>
>>>
>>>
>>> Full code:
>>>
>>> Sub MakeReferenceWkbk()
>>>
>>> 'default start path, editable by user from the filepicker dialogue
>>> PathOnly = "\\wsak1\Perf\Score\Ops\People\Increases"
>>>
>>> Dim I As Integer
>>> Dim owb As Workbook 'original/main
>>> Dim twb As Workbook 'temp/data file
>>> Dim ows As Worksheet
>>> Dim tws As Worksheet
>>>
>>> Set owb = ActiveWorkbook
>>> Set ows = ActiveWorkbook.ActiveSheet
>>>
>>> Dim SaveDriveDir As String
>>> 'save default path
>>> SaveDriveDir = CurDir
>>>
>>> TitleString = "Please select the Raw data file"
>>>
>>>       'change to new path
>>>       DirectorySetPath (PathOnly)
>>>
>>>       'get the file
>>>       newFN = Application.GetOpenFilename(FileFilter:="Excel-Compatible Files,
>>> *.xls;*.xlsx;*.xlsm;*.csv", Title:=TitleString)
>>>       If newFN = False Then
>>>           ' They pressed Cancel
>>>           MsgBox "Stopping because you did not select a file"
>>>           'return to original default path
>>>           DirectorySetPath (SaveDriveDir)
>>>           Exit Sub
>>>       Else
>>>           MyFullFilePath = newFN
>>>       End If
>>>
>>> Application.StatusBar = "Opening File "&   MyFullFilePath
>>>
>>> 'Open source workbook
>>> Application.DisplayAlerts = False
>>> Set twb = Workbooks.Open(Filename:=MyFullFilePath, UpdateLinks:=0,
>>> ReadOnly:=True)
>>> Application.DisplayAlerts = True
>>> twb.Activate
>>> twb.Sheets(1).Activate
>>>
>>> 'update the file
>>>       For I = 1 To 6
>>>           ActiveWorkbook.Sheets.Add
>>>       Next
>>>
>>>       shtNameArr = Array("NWR", "NAR", "SWR", "SAR", "WWR")
>>>
>>>       For I = 1 To 5
>>>           ActiveWorkbook.Sheets(7).Select
>>>           ActiveWorkbook.Sheets(7).Cells.Select
>>>           Selection.AutoFilter
>>>           Selection.AutoFilter Field:=9, Criteria1:=shtNameArr(I)
>>>           ActiveWorkbook.Sheets(7).Cells.Select
>>>           Selection.Copy
>>>           ActiveWorkbook.Sheets(I).Select
>>>           ActiveSheet.Paste
>>>           LRow = lastRow(Sheets(I))
>>>           ActiveSheet.Name = shtNameArr(I)
>>>           ActiveSheet.Cells.Select
>>>           ActiveSheet.Cells.EntireColumn.AutoFit
>>>           ActiveSheet.Range("V1").Select
>>>           Application.CutCopyMode = False
>>>           ActiveCell.Value = "Days Late"
>>>           ActiveSheet.Range("V2").Select
>>>           ActiveCell.Formula = "=IF(M2>L2,M2-L2,"""")"
>>>           ActiveSheet.Range("V2").Select
>>>           Selection.AutoFill Destination:=Range("V2:V"&   LRow)
>>>           ActiveSheet.Range("A1").Select
>>>       Next
>>>
>>>           ActiveWorkbook.Sheets(7).Select
>>>           ActiveWorkbook.Sheets(7).Cells.Select
>>>           Selection.AutoFilter
>>>           Selection.AutoFilter Field:=9, Criteria1:="STTC"
>>>           Selection.AutoFilter Field:=18, Criteria1:="=03", Operator:=xlOr,
>>> Criteria2:="=04"
>>>           ActiveWorkbook.Sheets(7).Cells.Select
>>>           Selection.Copy
>>>           ActiveWorkbook.Sheets(6).Select
>>>           ActiveSheet.Paste
>>>           LRow = lastRow(ActiveWorkbook.Sheets(6))
>>>           ActiveSheet.Name = "STTC"
>>>           ActiveSheet.Cells.Select
>>>           ActiveSheet.Cells.EntireColumn.AutoFit
>>>           ActiveSheet.Range("V1").Select
>>>           Application.CutCopyMode = False
>>>           ActiveCell.Value = "Days Late"
>>>           ActiveSheet.Range("V2").Select
>>>           ActiveCell.Formula = "=IF(M2>L2,M2-L2,"""")"
>>>           ActiveSheet.Range("V2").Select
>>>           Selection.AutoFill Destination:=Range("V2:V"&   LRow)
>>>           ActiveSheet.Range("A1").Select
>>>
>>>       ActiveWorkbook.Sheets(1).Activate
>>>       Application.DisplayAlerts = False
>>>       ActiveWorkbook.Sheets(7).Delete
>>>       Application.DisplayAlerts = True
>>>
>>> 'Select/copy a single cell to avoid clipboard warnings
>>> ActiveSheet.Range("A1").Copy
>>>
>>> ''close the workbook to get it out of the way
>>> 'Application.DisplayAlerts = False 'just in case the clipboard trick doesn't
>>> work
>>> 'twb.Close SaveChanges:=False
>>> 'Application.DisplayAlerts = True
>>>
>>> Application.StatusBar = False
>>>
>>> sDate = Year(Now())&   Format(Month(Now()), "00")&   Format(Day(Now()), "00")
>>> ShortFileName = ExtractFileName(MyFullFilePath)
>>>
>>> 'get the month "name" for the data set being saved, to put it in the filename
>>> PasteMonths = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
>>> PasteMonths2 = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug",
>>> "Sep", "Oct", "Nov", "Dec")
>>> PasteMonthNum = CInt(InputBox("Enter the month number represented by this
>>> data (e.g. 1 for Jan, 2 for Feb, etc)", "Enter Data Month"))
>>> If IsError(Application.Match(PasteMonthNum, PasteMonths, False)) Then
>>>       MsgBox "Unable to recognize a date from 1 to 12."&   Chr(13)&   Chr(13)&
>>> "Output file not saved; please run again to finish processing", , "Month
>>> Number Error"
>>>       Exit Sub
>>> Else
>>>       StrMonth = PasteMonths2(PasteMonthNum)
>>> End If
>>>
>>> DirectorySetPath ("\\wsak1\Perf\Score\Ops\Public\CurrentData")
>>> 'GetSaveAsFilename
>>> new2fn = Application.GetSaveAsFilename( _
>>>               InitialFileName:="2010 Ops Increases - "&   PasteMonthNum&   ""&
>>> StrMonth, _
>>>               FileFilter:="Excel-Compatible Files, *.xls;*.xlsx;*.xlsm;*.csv", _
>>>               Title:=TitleString)
>>>
>>> ActiveWorkbook.SaveAs Filename:=new2fn
>>>
>>> 'return to original default path
>>> DirectorySetPath (SaveDriveDir)
>>>
>>> 'PullAllRawData = Now()
>>> MsgBox "Source data file has been successfully created and saved"
>>>
>>> OldShortFN = ExtractFileName(newFN)
>>> OldPathN = Left(newFN, Len(newFN) - Len(OldShortFN))
>>> Debug.Print OldPathN&   OldShortFN
>>>
>>> Name newFN As OldPathN&   OldShortFN
>>>
>>> End Sub
>> .
>>
0
Jon
6/1/2010 5:16:40 PM
Reply:

Similar Artilces:

zip codes don't merge #2
I am trying to mail merge w/ Word 2000 the names and addresses in my worksheet. When I get to the part to choose the format for the mailing labels, I choose F1, F2, etc. to F6 (which is the zip code column). A few do get there, but the vast majority stop at the state, leaving off the entire zip code. I have gone to menu/format and selected text in the number tab. I have gone to format/cells and chosen special/zip code in the number tab. I've read Excel for Dummies. Please help me. TIA bb ...

keeping two computers updated with one money file
I use a desktop at home and a laptop when I am on the road. What I would like to do is be able to keep my money file syncronized between my two computers. Money offers nothing that will help with this. There is no good way to do this short of something like WinXP Synchronization Manager. I can't say as I've tried it or read here of someone who has. Is that an answer to the question you never asked? "JB" <JB@discussions.microsoft.com> wrote in message news:95B9AF2E-EBD5-4D09-826A-1430BF6C035E@microsoft.com... >I use a desktop at home and a laptop when I am on ...

How to see files in .docx?
I have read that .docx has many files compressed like .zip files, but where are they? How to see them? Thanks You will have to change the file name extension from docx to zip. Then open the zip file. -- Stefan Blom Microsoft Word MVP "Ghitorni" <padhye.m@gmail.com> wrote in message news:evrVaQIvKHA.5036@TK2MSFTNGP02.phx.gbl... >I have read that .docx has many files compressed like .zip files, but where >are they? How to see them? > Thanks > Or open docx directly using winrar -- <>>< ><<> ><<...

Re: merging a pub file into a pub document?
GbH wrote: > I'm sure I've seen this somewhere in here or a similar newsgroup. > Someone clever has written a script that will import a publisher > document into an existing/open publisher document. > Please can somebody point me at it. > > -- Also why did my post seem to disappear off the server before I could read it? -- -- Wisdom and experience come with age, they say, but I wish I could remember the darn question Both of your posts show -- Don Vancouver, USA up here. "GbH" <Geoff_Hannington@IEE.ORGasm> wrote in message news:u7JmyS0d...

Integrate Paycodes, benefit codes and deduction codes
Has anyone used integration manager to update new pay rates, deduction amounts and benefit amounts for employees? At the beginning of each year, our company gives pay increases and we need to update the pay codes, deduction codes and benefit codes for 40 employees, which we get the information from a spreadsheet. I thought that maybe I could use integration manager to update the pay, benefit and deduction codes instead of going into each employee's card, which is time consuming. Thanks, Laura Integration Manager will allow you to do this. Use the Payroll Master Destination. one ...

Can't delete file
I just did a system recovery on a Gateway with XP Pro from the restore partition on the hard drive. One of the restore options was to save the old windows files in a folder called Backup (date). So I did that. After restoring the OS I copied all the files I needed back into the new install from the backup folder. Then I wanted to delete the backup folder. Everything deleted OK except... C:\Backup\Windows\System32\Macromedia\Flash\flash8a.ocx No way in hell can I get it deleted. "access denied". I booted into Safe Mode where I could get to the security options and no mat...

concatenating two vendor codes
My company recently changed the vendor codes and now I end up with two separate sets of data for each vendor. Obviously I could just leave the vendor code field out. I don't want to do that so what I need to do is concatenate the two codes. Example Vendor Code Vendor Name 123 Joe's Supplies ABC Joe's Supplies what I need is this Vendor Code Vendor Name 123/ABC Joe's Supplies any ideas? Rather than create a new record for each vendor, add another field to the table -- call it NewVendorCode -- and put th...

Can MS EXCEL remove duplicates and separate by color coded items ?
Can MS EXCEL remove duplicates and separate by color coded items ? I am NOT technical and have just started using MS EXCEL. Can anyone PLEASE HELP me: 1. How can I automatically remove duplicates using EXCEL ? ie the same info input more than once on different lines. Can EXCEL do this ? 2, I have color coded the text in the certain lines in terms of priority. can Excel rearrange the data by color ? If yes, How do I do it ? I think I have EXCEL '97 Thanks for your help in advance. Hi 1. You can extract the unique items to a new list using menu Data > Filter > Advanced fil...

how to write data to file
I wonder if anyone knows a simple way to write a selection of fields from a single record out to a local file on the server. I then need to automate this in a simple way. click a button or workflow process. Thanks this is a job for the SDK..pretty simple but requires coding ============================== John O'Donnell Microsoft CRM MVP http://www.crowecrm.com "DMcL" <DMcL@discussions.microsoft.com> wrote in message news:F3542CF5-1340-4167-9566-047B5AFDE409@microsoft.com... >I wonder if anyone knows a simple way to write a selection of fields from a > single...

attach pdf file
I am not sure if this is possible. It is kind of an odd situation. We are in the process of data dumping some document numbers and images. The images or PDF files. What they are asking me to do is build a DB that contains the document numbers and also attach the PDF images that are associated with each doc number. This is so another dept can view the information for doc number and PDF file at the same time. Is this possible? Have a look at the sample database: http://www.datastrat.com/Download/DocMgr_2K.zip If I remember correctly, it doesn't include PDFs, but they ...

16 bit code
Hello, I have a 16 bit code to maintain. (It is too complex to copile it to 32 bit) so all we are doing is support. Now they want to add a small feature, where I need to copy long file names in the project. I know we can not do long file name copies in vc++ (1.52). So I wrote a program in VC++(6.0) which does directory copy. My questions are 1. How can I call a 32 bit exe (I guess WinExec should work)? 2. How can I make the 16 bit to wait till the 32 bit code completes? 3. Is there any other better way to do this? (other than converting to 32 bit) Thanks. I actually do this in an old 16-bi...

File I/O related
Hi I have this file i/o related problm I need to create a U I which adds, updates and deletes records from a text file The problm is i need to del/modify records in between using the interface Is it possible to get to the exact position sey 15th record-->to be updated or deleted The problm of multiple primary keys also is there.I have certain data files which have multiple primary keys.Is it possible in MFC to develp using CFile etc.... i don't know ,sorry These requirements clearly suggests, you need a database....( Use MS Access ) If you want to use files, just do something like...

folder deleted while trying to delete one file in Outlook Express
I wanted to delete one file but instead it deleted the entire folder. It was not sent to the deleted folder nor to the recycle bin. Why did this happen? More important is there anyway that I can retrieve the files in the folder. Thanks in advance. Gil Hoellerich It happened cause you pressed delete and then confirmed it by pressing yes. I don't believe there is any way to recover it other than by restoring from backup. "G Hoellerich" <ghoellerich@NC.RR.com> wrote in message news:01de01c35a02$42738fd0$a101280a@phx.gbl... > I wanted to delete one file but instead it d...

Dynamic range from a different XLS file for a pivot table
Hello. I have several pivot tables in an Excel-1 file. They all aquire the data from another Excel-2 file where I have a List created, which enlarges everytime I add more data to it. I have created a dynamic name in Excel-2 as explained in http://contextures.com/xlPivot01.html. I would like to use the name of the data range in Excel-2 in the pivot tables as its source data in Excel-1. Unfortunately, when I try to use that name as an external source for the pivot tables, it doesn't appear. Do you know how could I do that? Maybe I could create some cell with the data range in Excel...

area codes is auto filling my own 9 digit ph# vs just area code
When I enter a phone number for a contact it autofills with my personal area code and phone number instead of just the area code. How to I change this to just autofil the area code? I am using Outlook 2007 on an ACER laptop. Make sure you have your area code entered correctly in "Dialing Properties". "Computer Dummy" wrote: > When I enter a phone number for a contact it autofills with my personal area > code and phone number instead of just the area code. How to I change this to > just autofil the area code? I am using Outlook 2007 on an ACER ...

Access file types?
I have created an Access database and have designed a form. When selecting "Form View" the forms remains in design view. I then get a new database which is an LDP file which I cannot delete and restricts using the mdp file. Why can I not view the designed form in Form View? It's LDB, not LDP; and MDB, nor MDP The LDB file is a locking database. It cannot be deleted while the main database is open. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.accessmvp.com http://www.mvps.org/access "Wayne Viles" <WayneViles@discussions...

VBA Code for Pasting Sheets
I would like a spreadhseet that pastes the contents of one sheet into another sheet. I like like to do this for 7 different sheets For example: I would like paste the contents form sheet titled "sheet1" into a sheet titled "data1". Continue to process for pasting "sheet2" into "data2" and "sheet3" into "data3" all way until "sheet7" and "data7". thanks, Curt Subject: Automated Copy Paste Subject: Copy/Paste Import/Export Data VBA Code On Apr 27, 10:49=A0am, Curt <C...@discussions.mi...

open/import Lotus wk4 files into Excel 2007 w\ formulae intact
I do not access to Lotus Smartsuite and need to import an old Lotus wk4 spreadsheet into Excel with the formuale intact. It would be huge hassle to recreate it and I am not going to invest in Smartsuite. thanks hi 2007 does not support lotus. so you need to find someone with lotus or check in to open office. it's free. http://openoffice.org regards FSt1 "jrj19" wrote: > I do not access to Lotus Smartsuite and need to import an old Lotus wk4 > spreadsheet into Excel with the formuale intact. It would be huge hassle to > recreate it and I am not going to invest i...

MC/VISA joint code
Isn't there any way to combine the blocks (4* & 5*) for MC/VISA credit cards? It's a pain to have them separate because my bank posts them together. It is the same company afterall. It would save me a lot of time adding separate entries together to reconcile the statement! I do not know of a way to do what you are asking, but I have found that using the # symbol as follows works better than using the * symbol: 4############### 5############### If you use the * symbol it is a wild card with no determined length. The # symbol requires that the characters after the first d...

Money 2004 - Experian Offer
I just loaded Money 2004 Deluxe and converted my 2003 files with no problem. I decided to give the free year of credit monitoring from Experian a try but after filling everything out I get an error that the provided code has expired. I neither had nor provided any code for this offer, I only used the link from Money 2004. Now Experian was more than willing to set up a $79 account if I wanted to proceed. Any solution out there? I have the same problem... I am looking for a FREE way to contact Microsoft about this problem. >-----Original Message----- >I just loaded Money 2004 ...

Saving OFX files from California Bank Of America...convert ofx?
All, I've seen similar posts, but not exactly the same. I saved an OFX from California BofA, and I can no longer retrieve the data in a different format (it's older than their three month deadline). When I try to import it, I get the familiar 'There is a problem with the data received from this online provider. If the problem persists, call the provider's customer service number for help.' Can I convert the OFX to OFC or QIF (which had worked for me previously). It seems like there are conversion utilities for almost every other format to OFX, but not from ...

files in web page not located in expected location
I have created a web publication and when I "publish to the web" the home page comes up just fine. However when I click on the next page which is a calendar, I get a message that says "some of the files in this web page aren't in the expected location. Do you want to download anyway?" When I say yes it shows the page, but not the way it is supposed to look. The calendar is larger than the page. This did not happen on my orignal document, but started happening after I had uploaded the initial index.html page to the web server. How can I fix this before I update ...

active window behind inactive, & other probs; font corruption?
Version: 2008; operating system: Mac OS X 10.5 (Leopard); processor: PowerPC I was referred here from another support list, because the expert advising me there suggested that I might have complicated subtle font problems; I am very grateful for any advice! This message is long, because several things are going on and I've tried several solutions. I routinely work with six or so Word files open, half of which have Track Changes turned on. I recently installed Word 2008 (upgrading from Word X), and have been having strange problems. When I select a group of files in the Finder ...

Analytical Accounting
Hi, One of my Client's is considering to go down AA path with approx 300,000 AA dimension codes. I would like to hear whether such data set is manageable from Query wizard etc. appreciate your thoughts. Good Morning PR, I found that the issue isn't the number of AA Trx Dim Codes it is the size of the AAG30000 and AAG40000 tables the more entries the longer it takes to product the report. I had one client that started having a cached SSRS report with AA information as a management level report and a different SSRS report with a more restrictive dataset for reporting ...

creating a file
Hi all; I wrote a program that it must create a file & it doesn=92t has error but at its runtime it has this error Unhandled exception :system. Index out of range exception :index was outside the bounds of the array using System; using System.IO; namespace ConsoleApplication85 { class Program { static void Main(string[] args) { StreamWriter myfile =3D File.CreateText(args[0]); myfile.WriteLine("*********"); myfile.Close(); Console.WriteLine("****"); } } } ...