Results 1 to 2 of 2

Thread: error when saving file in xls and pdf format
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2011
    Location
    mauritius
    Posts
    187
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default error when saving file in xls and pdf format

    Dear Guys

    Hi i am having the following problem with below vba codes when saving file to xls and pdf

    Error

    Run time Error 1004. Document not saved. The document may be open , or an error may have been encountered when saving.

    My vba codes are as follows :-

    Sub searches()
    Range("b2") = Format(Date, "dd/mmm/yyyy")
    Const mydrive = "C:"
    Const mydir = "Users\hemraz\Desktop\searches\Rakesh Searches\2019"
    Dim myname As String
    Dim ms As String
    myname = Sheets("sheet1").Range("e5").Text & Format(Date, "dd-mmm-yy") & ".xls"
    ms = mydrive & "" & mydir & "" & myname & ".xls"
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=ms
    Application.DisplayAlerts = True
    Dim I As Long, c As Range
    For I = 1 To 2
    Set c = Application.InputBox("Click in the cell to insert the picture", Type:=8)
    ActiveSheet.Pictures.Insert ("C:\Users\hemraz\Desktop\searches\sign.bmp")
    With ActiveSheet.Shapes(ActiveSheet.Shapes.Count)
    .LockAspectRatio = False
    .Top = c.Top
    .Left = c.Left
    .Height = c.RowHeight
    .Width = c.Width
    End With
    Next I


    Dim mydir1 As String
    Dim myname1 As String
    Dim ss As String
    Const mydrive1 = "C:"




    mydir1 = "Process"
    Select Case Range("D2").Value
    Case "CG"
    mydir1 = mydir1 & "\CHEMIN GRENIER"
    Case "CB"
    mydir1 = mydir1 & "\CORPORATE BANKING"
    Case "CR"
    mydir1 = mydir1 & "\CREDIT ADMINISTRATION"
    Case "CP"
    mydir1 = mydir1 & "\CUREPIPE"
    Case "FL"
    mydir1 = mydir1 & "\FLACQ"
    Case "GD"
    mydir1 = mydir1 & "\GOODLANDS"
    Case "HO"
    mydir1 = mydir1 & "\HEAD OFFICE"
    Case "HR"
    mydir1 = mydir1 & "\HR"
    Case "IB"
    mydir1 = mydir1 & "\INTERNATIONAL BANKING"
    Case "LS"
    mydir1 = mydir1 & "\LESCALIER"
    Case "M"
    mydir1 = mydir1 & "\MAHEBOURG"
    Case "PB"
    mydir1 = mydir1 & "\PRIVATE BANKING"
    Case "QB"
    mydir1 = mydir1 & "\QUATRE BORNES"
    Case "RW"
    mydir1 = mydir1 & "\RECOVERY"
    Case "SME"
    mydir1 = mydir & "\RETAIL(SME)"
    Case "RDR"
    mydir1 = mydir1 & "\RIV DU REMPART"
    Case "RB"
    mydir1 = mydir1 & "\ROSE BELLE"
    Case "RH"
    mydir1 = mydir1 & "\ROSE-HILL"
    Case "TR"
    mydir1 = mydir1 & "\TRIOLET"
    Case "V"
    mydir1 = mydir1 & "\VACOAS"
    Case "CC"
    mydir1 = mydir1 & "\CONTACT CENTRE"
    End Select




    Range("b2") = Format(Date, "dd/mmmm/yyyy")
    myname1 = Sheets("sheet1").Range("e5").Text & Format(Date, "dd-mmm-yy") & ".pdf"
    'ss = mydrive & "" & mydir & "" & myname
    ss = mydrive1 & "" & mydir1 & "" & myname1 & ".pdf"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ss, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
    False




    'Application.DisplayAlerts = False
    'ActiveWorkbook.SaveAs Filename:=ss
    'Application.DisplayAlerts = True




    End Sub

    When i run the macro the file is saved in xls format in the folder Users\hemraz\Desktop\searches\Rakesh Searches\2019 but not in pdf format in folder mydir1 = "Process".

    Your help will be highly appreciated

    thanks/regards

    rakesh

  2. #2
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,188
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: error when saving file in xls and pdf format

    You have
    mydrive = "C:"

    This is concatenated with mydir or mydir1.
    All references to mydir and mydir1 do NOT include the \ at the beginning.

    Insert the \ after the colon in mydrive.

    Same with mydrive1.

    See if that works.
    Last edited by Special-K99; Sep 19th, 2019 at 10:50 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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