error when saving file in xls and pdf format

rakesh seebaruth

Active Member
Joined
Oct 6, 2011
Messages
303
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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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:
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top