HELP!!! Problem saving file in excel

[grego9]

I am running a macro from an excel file called "MASTER - FX Reconciliations Modelvista.xls" (excel 2010 in compatibility mode to save in 2003 format) from a button in the spreadsheet. The macro takes data from a file called "rollifrp.xls" (after a small amount of manipulation) and pastes it into the
"MASTER - FX Reconciliations Modelvista.xls" file. It then attempts to save the rollifrp file down with a new name dependent on the content of cells E4 and E6 on the Index tab of the "MASTER - FX Reconciliations Modelvista.xls" file. This renaming and saving is NOT successful - I get a run time error 1004 "document not saved". When I then debug and choose the run to cursor option and run the macro to the end it does exactly what I want it to. Why do I get the run time error and how can I get rid of it - it is driving me mad!! I've checked out the microsoft site but the content did not seem relevant. The code is below - any help greatly appreciated!

Sub FXcurr()
 
    Application.ScreenUpdating = False
 
    Dim stDocName As String
    Dim stDocName2 As String
    Dim AP As Range
    Dim CurrYr As Range
 
    Application.ScreenUpdating = False
 
    Set AP = Worksheets("Index").Range("E4")
    Set CurrYr = Worksheets("Index").Range("E6")
 
  ' rollifrp excel details only
 
    stDocName = "Q:\Reports\Financial Reporting\IFRS\Models\rollifrp.xls"
 
    Workbooks.Open stDocName
 
    Windows("rollifrp.xls").Activate
    Cells.Select
        With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Rows("1:10").Select
    Selection.Delete shift:=xlToUp
 
    Columns("F:F").Select
    Selection.Delete shift:=xlToLeft
 
 
'   Copy data to master file
 Range("A:L").Select
    Selection.Copy
    Windows("MASTER - FX Reconciliations Modelvista.xls").Activate
    Sheets("rollifrp").Select
     Range("A1").Select
 
    ActiveSheet.Paste
    Application.CutCopyMode = False
 
'   Reformat original file for saving in AP folders
    Windows("rollifrp.xls").Activate
 
    Sheets("Sheet1").Select
ActiveSheet.Range("A:L").Select
 
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
         Range("A1").Select
 
 
    If AP.Value < 10 Then
        stDocName2 = "Q:\Reports\Financial Reporting\IFRS\" & CurrYr & "\AP0" & AP.Value & "\04_Models\rollifrp AP0" & AP.Value & ".xls"
    Else
        stDocName2 = "Q:\Reports\Financial Reporting\IFRS\" & CurrYr & "\AP" & AP.Value & "\04_Models\rollifrp AP" & AP.Value & ".xls"
    End If
 
 
 
 
ActiveWorkbook.SaveAs stDocName2

[twinnyfo]

Grego9,

First, please use the code tags when posting your code, it makes it easier for us to follow.

Second, I am assuming the error actually fires on your last line of code:

ActiveWorkbook.SaveAs stDocName2

Is this correct?

My initial thought is that the file name (particularly the path), does not currently exist. You must make the directory into which you are trying to save the file, first. If the directory exists when you try to create it, you will get an Error 13, but if you trap that particular error, and resume next, all should work fine.

In your code you will need the following in strategic points:

Dim strPath As String
 
strPath =  "Q:\Reports\Financial Reporting\IFRS\" & _
    CurrYr & "\AP0" & AP.Value & "\04_Models\"
 
OR...
 
strPath = "Q:\Reports\Financial Reporting\IFRS\" & _
    CurrYr & "\AP" & AP.Value & "\04_Models\"
 
MkDir strPath

This should get you headed in the right direction (I hope).

[grego9]

Thanks for the reply. However, the directory already exists and the code can be executed fine when I simply go to debug and set run to cursor to the last line of code. It does everything I want it to - the only thing I don't want it to do is pop up the run time error when running the macro each time!

Thanks!

[twinnyfo]

Hmmmmmmmmmmmm, very strange.

In my Excel automation, I've not run across a problem "Saving As..." My Excel manipulation is always done from MS Access, but the VBA should be nearly identical.

From a troubleshooter's perspective, I would try to open, manipulate and save some test files from within the same code, to determine if it is a problem with the files you are using or the code. Sometimes when I use very simple code to work with test files, I uncover a particular point elsewhere in my code that "appears" to work fine, but causes problems like this.

I wish I had a better answer, but your code looks to to fine.

[grego9]

I think the problem is regarding which sheet is active. If I put
ThisWorkbook.Activate before ActiveWorkbook.SaveAs StDocName2 the code saves the "MASTER - FX Reconciliations Modelvista.xls" with the right name and in the right location - However it is the rollifrp.xls file that I want saved down. A bit strange as I have the
Windows("rollifrp.xls").Activate
command in the code. Do you think this would be best placed in the Excel forum rather than VBA?
Thanks for looking at this for me.

[twinnyfo]

I think you've hit on one of the challenges I've run across with automating Excel, too. Sometimes it is difficult to keep track of which file the system wants to work with.

Try "selecting" the workbook you wnat to save first:

Sheets("Sheet1").Select

And then try the SaveAs again.

This thread could go in either the Excel Forum or VBA. Excel (and all MS Office) "macros" are essentially VBA. Your "VBA" question deals specifically with MS Excel. But, I will let the moderators choose which forum best capturees the nature of your question.

[grego9]

Thanks - I've tried that and it still falls over. This code works elsewhere in the same workbook - bit of a puzzler. I've even tried picking up a different file and then saving that down instead - but it still falls over!
Thanks for your persistence on this one!
I might have to take a break before I go insane!

[twinnyfo]

Have you tried closing the other file (MASTER - FX Reconciliations Modelvista.xls), first?

But, I feel you--the more time I work with VBA, the more trips I must make to the asylum. :-)

[grego9]

I re-engineered the solution. I copied the contents of the rollifrp.xls into the Master spreadsheet and then "copied" that tab to create a new file with the naming convention outlined above. Worked a treat. Shame I couldn't figure out how to address the original problem - but at least I will sleep at night now.

[grego9]

Thanks for your help twinnyfo - you got me thinking differently about how to solve it!

[twinnyfo]

Glad I could be of service. Sometimes the solution is not he "right way" but at least solves the problem. let us know if we can help with anything else.