Excel Sheet Wont Open In 2007.

[anoble1]

Hopefully I can explain this.
I have a main excel sheet that when it opens it grabs data from 8 other spread sheets and updates numbers on the main one. Well, when it opens in Excel 2007
I get a error message 'C:\/" cannot be accessed. The file may be corrupted, located on a server that is not responding, or read-only.

In the code it is saving a copy to the C: so it can be opened multiple times by different users.

Public Function GetData()
'Initially, this takes the spreadsheet and copies it to the C: drive
'This is done, so many people can access it at the same time
'Note:  I had to create a few functions, so if you see something you don't recognize,
'Look further downS
 
        'If the spreadsheet is a copy
        If ActiveWorkbook.path <> "C:" Then
            path = pathName
        'If it isn't, then assign the variable path the value of the path on the s drive
        Else
            path = "S:\Workgroups\APC Power Delivery\STORM\Southern Company Shared Data\Contractor Contact Assignments\"
        End If
 
        'This causes Excel to not display the "You are about to delete" message
        Application.DisplayAlerts = False
 
        'Save a copy to the C drive
        ActiveWorkbook.SaveAs "C:/" & getName
 
        'Display the caption
        frmWait.Show
        frmWait.Label1.Caption = "Initializing"
        frmWait.Repaint
 
 
        Dim i As Integer
 
        'initialize the variables
        Dim opCo As Integer
        opCo = 1
 
        Dim linePlaceHolder As Integer
        Dim treePlaceHolder As Integer
        Dim oldLinePlaceHolder As Integer
        Dim oldTreePlaceHolder As Integer
 
 
        Dim destSheet As Worksheet
        Dim destsheet2 As Worksheet
        Dim copysheet As Excel.Worksheet
        Set destSheet = ActiveWorkbook.Worksheets(1)        'This worksheet is the one for the line contractors
        Set destsheet2 = ActiveWorkbook.Worksheets(2)       'This one is the worksheet for the tree contractors
 
        'They are currently protected, this unprotects them
        destSheet.Unprotect "pdcs"
        destsheet2.Unprotect "pdcs"
 
        'Delete all current data, except for the the headers, which start at row 5
        destSheet.Range("A5:AU10000").EntireRow.Delete
        destsheet2.Range("A5:AU10000").EntireRow.Delete
 
        'Initializes for working in Excel, using vba
        Dim appExcel As New Excel.Application
        Dim wkbk As Excel.Workbook
 
        Set appExcel = Excel.Application
 
        'Set place holder to 5, which is the row that the first data line will appear on
        linePlaceHolder = 5
        treePlaceHolder = 5
 
        'Go through list of operating companies (opCo's)
        Do While opCo < 5
            ' Get OpCo's Lines
 
            'Adjust the label caption
            frmWait.Label1.Caption = "Getting " & getOpCos(opCo) & "'s info."
            frmWait.Repaint
 
            'Open the workbook for the appropriate file, which will be the line workbook for the appropriate opco
            Set wkbk = appExcel.Workbooks.Open(path & getFileName(opCo, "Line"), , True)
            'Set variable copysheet, which contains the source data to the first worksheet
            Set copysheet = appExcel.Sheets(1)
 
            'Sets variable, oldLinePlaceHolder to whatever the previous placeholder was, the inital value will be 5
            oldLinePlaceHolder = oldLinePlaceHolder + linePlaceHolder
            'Readjust linePlaceHolder to when "End" appears in the spreadsheet
            linePlaceHolder = getStoppingRow(4, opCo, "Line")
 
            'Copy from what was in the range from "A6"(starting for its data) to whenever "End" was reached,
            'Then place it into the value, starting with whatever the old placeholder was
            copysheet.Range("A6:AW" & linePlaceHolder - 1).Copy destSheet.Cells(oldLinePlaceHolder, 1)
 
            opColors(opCo) = copysheet.Range("A6:A6").Interior.ColorIndex
 
            'Clean blank lines
            CleanLines destSheet, oldLinePlaceHolder
 
            'close the opco line workbook
            wkbk.Close
 
' Get OpCo's Trees  - now we repeat for the tree contractors
 
            'Open the workbook for the appropriate file, which will be the tree workbook for the appropriate opco
            Set wkbk = appExcel.Workbooks.Open(path & getFileName(opCo, "Tree"), , True)
            'Set variable copysheet, which contains the source data to the first worksheet
            Set copysheet = appExcel.Sheets(1)
 
            'Sets variable, oldTreePlaceHolder to whatever the previous placeholder was, the inital value will be 5
            oldTreePlaceHolder = oldTreePlaceHolder + treePlaceHolder
            'Readjust treePlaceHolder to when "End" appears in the spreadsheet
            treePlaceHolder = getStoppingRow(4, opCo, "Tree")
 
            'Copy from what was in the range from "A6"(starting for its data) to whenever "End" was reached,
            'Then place it into the value, starting with whatever the old placeholder was
            copysheet.Range("A6:AW" & treePlaceHolder - 1).Copy destsheet2.Cells(oldTreePlaceHolder, 1)
 
            'Clean blank lines
            CleanLines destsheet2, oldTreePlaceHolder
 
            opColors(opCo) = copysheet.Range("A6:A6").Interior.ColorIndex
 
            wkbk.Close
 
            'Increment to next opco
            opCo = opCo + 1
        Loop
 
        frmWait.Label1.Caption = "Finishing."
        frmWait.Repaint
 
        'Set appropriate colors for both sheets
        setColors destSheet
        setColors destsheet2
 
        'Clean blank lines
        CleanLines destSheet, oldLinePlaceHolder
        CleanLines destsheet2, oldTreePlaceHolder
 
        'Set lable of when the spreadsheet was last updated
        destSheet.Range("C1") = "Last updated at " & Now
        destsheet2.Range("C1") = "Last updated at " & Now
 
        'Adjust width of all columns for both sheets
        destSheet.Columns.AutoFit
        destsheet2.Columns.AutoFit
 
        'Protect them, and save with previous password
        destSheet.Protect "pdcs", True, True, True
        destsheet2.Protect "pdcs", True, True, True
        ActiveWorkbook.Save
 
        'Hide form, and redisplay alerts again
        frmWait.Hide
        ActiveWorkbook.SaveAs "C:/" & getName
        Application.DisplayAlerts = True
 
End Function
 

[CyberSoftHari]

Try yourself and post your query or part of code which gives error. Do not expect that anyone here to execute your entire code and solve for you.

[Stewart Ross]

I echo CyberSoftHari's comment about proper testing and debugging yourself. However, why do you refer to the file on drive C in this way?

ActiveWorkbook.SaveAs "C:/" & getName

Surely it should be

ActiveWorkbook.SaveAs "C:\" & getName

You do this more than once, so make sure you change all occurrences in your code.

Debugging should be easier for you than for us - you can surely use the debugger to show you the values of variables when your code fails, and see for yourself that you've used a forward slash instead of a back slash character.

Also, the error message you received really was pointing you in the right direction - so you should have focused on the lines that involved saving the file to drive C!

-Stewart

[anoble1]

Sorry for all the confusion. I have been using this spread sheet for office 2003 for 5 years now and everything works PERFECT for 2007. I took your advise and changed the C:/ to C:\ and it worked perfectly. I guess I wasn't thinking when I wrote it. I am just wondering how it worked before lol.

Thanks for all the help!