• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 493
  • Last Modified:

Cannot Rename File Name Excel 2007 under Windows Server 2008r2

I am trying to run a very large Excel 2007 VBA program on a Windows Server 2008 r2.  For some reason I am not able to rename.  

I thought that it was a problem of other activity in the server that was affecting my program.  So I used a loop. The following is a snipet of the code that I have tried.  Unfortunately, that didn't work.

 Active.Select
    ActiveSheet.HPageBreaks(1).Delete
    ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
    Dim Newname As String
    Newname = Range("b1").Value
    Application.DisplayAlerts = False
    Do While ActiveWorkbook.Name = "Target.xls"
        On Error Resume Next
        ActiveWorkbook.SaveAs Filename:= _
            "F:\Shared Files\SALES\XYZ Reports\" & Newname & "WXY", FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
            CreateBackup:=False
        If ActiveWorkbook.Name = "Target" Then
            Sleep 15000
        End If
    Loop
    ActiveWorkbook.Close
0
KenZaw
Asked:
KenZaw
  • 5
  • 2
2 Solutions
 
sir plusSales ManagementCommented:
I presume you mean your code isnt working
Sleep needs to be referenced as its not native to vba

Put this into your Declarations section in either your module
or form/report source code:

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Here's a sample call:

Sleep (5000) ' Waits for 5 seconds

There was also an error on the first line & activeworkbook cannot be target but target.xls
Use a constant to avoid typiing mistakes

Why are you using activeworkbook?
This will only save it while target.xls is active
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub test()
const cNameTarg = "Target.xls"
  ActiveSheet.Select
    ActiveSheet.HPageBreaks(1).Delete
    ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
    Dim Newname As String
    Newname = Range("b1").Value
    Application.DisplayAlerts = False
    Do While ActiveWorkbook.Name = cNameTarg 
        On Error Resume Next
        ActiveWorkbook.SaveAs Filename:= _
            "F:\Shared Files\SALES\XYZ Reports\" & Newname & "WXY", FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
            CreateBackup:=False
        If ActiveWorkbook.Name = cNameTarg  Then
            Sleep 15000
        End If
    Loop
    ActiveWorkbook.Close

 End Sub

Open in new window

0
 
KenZawAuthor Commented:
Thank you for your respond.

I don't think that we have to address the timeouts.

I think the root problem is that the server cannot rename a file.    What causes the non-renaming?  The probram works probably when I use a computer running with XL.

0
 
KenZawAuthor Commented:
I meant my computer is running with XP, not XL.  Sorry.
0
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

 
sir plusSales ManagementCommented:
Ok
If its excel it may be that you have saved it already and so it became locked and in that case try saving it as a different file and then again as the file you want it to be.

If its not it then usually if a file cannot be saved it means the file is either reserved by the os or the drive/network its on can't be reached or the name is invalid/path too long.

Goint to assume its the first to begin with

Try to find a program called file unlocker which works with xp ince you install it (I have used it for years so it is safe) it will let you right click on a file

If you browse to the file in explorer and right click on the offending file while its displaying symptom unlocker will tell you what program had reserved your file, you can also unlock it using unlocker.

0
 
KenZawAuthor Commented:
We have decided to purchase another PC.  There are no problems when we run it on a standalone machine.

Thank you for your information.  The last entry seems to be a plausible, so I will close this as resolved.
0
 
KenZawAuthor Commented:
Good work.
0
 
KenZawAuthor Commented:
Good work
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now