+ Reply to Thread
Results 1 to 5 of 5

Another 1004 error problem

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    11-13-2007
    Location
    Petersfield, Hampshire, UK
    MS-Off Ver
    MS Office for Mac ver 16
    Posts
    135

    Another 1004 error problem

    I have an xlt file which when opened has a button to click to save the file as an xls in a specific sub-folder. I did have this code working but have been away for a month and on my return it seems to have developed the 1004 error.
    In the following code all the Ranges exist in the workbook and the worksheet is named Operation. The code fails at the line starting ActiveWorkbook
    Private Sub SaveFile_Click()
    '
    ' SaveFile Macro
    '
    Dim Dt As Date
    Dim Ex As String
    
    Dt = Range("StartDate").Value
    Ex = Left(ExIncOp, 2)
    '
            If IsEmpty(Worksheets("Operation").Range("ExIncOp")) Then
        Msg = MsgBox("Please enter TYPE in cell C6 to continue ", vbOKOnly)
            End If
            
            If IsEmpty(Worksheets("Operation").Range("Name")) Then
        Msg = MsgBox("Please enter NAME in cell H6 to continue ", vbOKOnly)
            End If
        
            If IsEmpty(Worksheets("Operation").Range("StartDate")) Then
        Msg = MsgBox("Please enter DATE FROM in cell E10 to continue", vbOKOnly)
            
    Exit Sub
            End If
    
        ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Range(Debrief_Folder) & Range("Ex").Value _
        & " " & Range("Name").Value & " " & Format(Dt, "dd-mmm-yy") & ".xls", FileFormat:=xlNormal
    
    End Sub
    The file should save into \"Debrief_Folder"+"1st 2 letters of variable Ex"+"Range Name"+"date in format 28-Feb-08"+".xls"

    Can anyone see what the problem is?

    I thought I had sorted this with previous help from this group
    Geoff Culbertson
    Petersfield, UK

  2. #2
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628
    I think the proble is when you use 'Debrief_Folder' as Range where you need to use ' " ' character:
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Range("Debrief_Folder") & Range("Ex").Value _
        & " " & Range("Name").Value & " " & Format(Dt, "dd-mmm-yy") & ".xls", FileFormat:=xlNormal
    Regards,
    Antonio

  3. #3
    Forum Contributor
    Join Date
    11-13-2007
    Location
    Petersfield, Hampshire, UK
    MS-Off Ver
    MS Office for Mac ver 16
    Posts
    135
    Antonio,

    Thanks for your reply, and yes Debrief_Folder should have been "Debrief_Folder", but it still doesn't work

    Regards

  4. #4
    Forum Contributor
    Join Date
    11-13-2007
    Location
    Petersfield, Hampshire, UK
    MS-Off Ver
    MS Office for Mac ver 16
    Posts
    135
    Further to my last I believe that .Value should be inserted after "Debrief_Folder") as follows, but it still doesn't work!

        ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Range("Debrief_Folder").Value & Range("Ex").Value & " " & Range("Name").Value & " " & Format(Dt, "dd-mmm-yy") & ".xls", FileFormat:=xlNormal
    Regards

  5. #5
    Forum Contributor
    Join Date
    11-13-2007
    Location
    Petersfield, Hampshire, UK
    MS-Off Ver
    MS Office for Mac ver 16
    Posts
    135
    I have now discovered that the problem in the code is
    Range("Ex").Value
    as
         ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Range _
         ("Debrief_Folder").Value & "\" & Range("Name").Value & " " & Format _
         (Dt, "dd-mm-yy") & ".xls", FileFormat:=xlNormal
    works as it is meant to.

    So why does the first bit of code not work?

    Ex = Left(ExIncOp, 2)
    defines Ex as a String and as the first 2 letters of whatever is in the range ExIncOp. Does this mean Ex is not a Range? as it is defined within the code?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1