• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to Copy Closed Excel File To another Folder With SaveAs with changed attributes?

C

Chirag R Raval

Member
Dear Sir,

I already start study at below links.

http://www.snb-vba.eu/VBA_Bestanden_en.html

http://www.cpearson.com/excel/docprop.aspx

http://www.globaliconnect.com/excel...workbooks-sendmail-method&catid=79&Itemid=475

If we open below link,there are like sea of VBA regarding huge knowledge.

http://www.snb-vba.eu/alfabet_en.html

but its take long time to understand whole concept.

I just want to copy my personnel.xlsb at "D:\Folder\" with New Name ,with current Date & Time (So each save is become unique, not overwritten previous), & remove hidden attributes. (with Remove tick mark on Hidden attribute)

I already open many sites but cant find solution of file save as With "SaveAs" method with closed workbook.

Can any idea?

Regards,

Chirag Raval
 
Last edited:
Dear Sir @Chihiro ,

thanks for your suggestion.

I found best link to understand copy file with fso at here.

https://wellsr.com/vba/2018/excel/vba-copy-a-file-with-fso-copyfile/
great learning...but ...there are one point there...

"Given a file by the same name as the source file already exists in the destination folder, is this file locked for editing by another application or user?This is only relevant if the OverWrite parameter has been set to True. Unfortunately, neither the standard Excel functions, the FSO nor any other intuitive object library includes a function for determining whether a file is already open. Thus, the easiest way to check this is to wrap the FSO .FileCopy function with On Error Resume Next and On Error Goto 0. If the file in the destination folder we’re trying to overwrite is locked for editing, it cannot be overwritten and attempting to do so will normally cause an error at run-time. However, with the On Error Resume Next statement, we tell Excel to ignore errors and we collect the description of any error we encounter with the Err.Description property of the Err object. Again, if the procedure encounters an error, it terminates with an error message explaining why it terminated. On a final note, it should be mentioned that source files that are locked for editing can be copied! "

How to unlock that file if excel hold after 1 attempt of copy?
OR
HOW TO UNLOCK THAT FILE FROM EXCEL BEFORE 2ND ATTEMPT OF COPY?

MY CODE & SCREEN SHOT IS HERE

ERROR.png


I also found very useful link here but can not handle this.

http://www.vbforums.com/showthread....e-a-folder-after-using-FileSystemObject-on-it

This case say some thing that if you use 1 folder 1 time, its locked by operating system or file system object, its stuck there, its continues hold it, we must divert attention or focus catch by FSo or operating system, from that location to another location as temporary, then re-divert its focus to our targeted folder for next operation

How to successfully copy as many time as wish without permission error?
Regards,

Chirag Raval
 
Last edited:
Why not check if destination file exist before you copy. If it does delete the destination file. And move over the new one. Or rename the destination with prefix/suffix, so that copied file has different name than destination?

But personally, I prefer not to use FSO, and just use FileCopy and/or Name. If I had to do file management in VBA.

In most cases, I prefer to use other tools for file management (PowerShell etc).
 
Dear Sir @Chihiro

Thanks for suggestion...I guess why my just copy 1 location to another is become hard? because i really stuck at below. i just use Sir ron's simple 1 liner code to copy.

ron.png


if we don't go far for solution, & want to check & if locked, unlock that location , is there are some code designed for that? or how can we use Powershell for that?

Really need help here.

Regards,

Chirag Raval
 
Hmm? If you use Ron's code, it will overwrite destination file if same file name exists.

PowerShell is completely separate code.

To check if file exists, you can use DIR().
Ex:
Code: 
Sub DEMO()
If Dir("C:\SomePath\Filename.xlsx") <> "" Then
    Debug.Print "File exists"
Else
    Debug.Print "File does not exist"
End If
End Sub
 
Dear Sir @Chihiro ,

I want to inform you (sorry to not mention at startup) that
Personnel.xlsb is the file from where this code run means
Code resides In personel.xlsb & want copy self to another
Location with new name & with unhidden.
(Remove hidden attribute)

So vba logic say we can use this workbook as method to use Save As so
We can use it with attribute.because it is currently open and activate.

How can any open file copy itself to another location?

If focus of system temporary jump out from itself & copy itself from outside there & return to Itself (but now where to come? In file that it previously execute command? Or in newly created file?


It's seems mysterying that how save as command do this trick ( copy self to new location & return back in original Or copy self to new location , stay in copied newly created file & close original from where it execute save as command) that really look mind blowing.

Can we say it temporarily take help third location to stay there / shift there/ jump there to do copy itself between two
Locations? If yes then what it that temporary
Staying point?

Can we say as below 3 steps?

(1) Temporarily shift focus / attention to temp location.

(2) Temporarily close previous file for micro second to copy it.

(3) After copy, either return open and return previous file.Or shift in newly created file and close previous.

I think it's take support 3rd location for temp stay & complete this process remotely from there ( may be temp stay in a clipboard) for micro seconds and return in either previous or new file.

Now seems i go towards Shell command?

What can we do here?

Regards,

Chirag Raval
 
Last edited:
Then it's rather simple...
Code: 
Sub BackupPMacrobook()
  Const bFile = "C:\Test\Backup\Personal.xlsb_"

  Application.DisplayAlerts = False
  With Workbooks("Personal.xlsb")
    .SaveCopyAs pFile & Format(Now(), "yy_mm_dd_hhmm") & ".bak"
    .Save
  End With
  Application.DisplayAlerts = True
End Sub
 
Dear Sir @Chihiro ,

Thank you sir, I will test & revert back soon .

Can you want to say power shell can handle
Many usefull operations like just copy of file regardless that file is open or closed,? If opened then it active or not active?

My humble request that if you can mention that little demo for
how can we copy file through PowerShell.

Regards,

Chirag Raval
 
Dear Sir @Chihiro

IJust Amazing..Your Code work like a Charm... I modified it ..

Code: 
'https://chandoo.org/forum/threads/how-to-copy-closed-excel-file-to-another-folder-with-saveas-with-changed-attributes.39836/#post-238412
Sub BackupPMacrobook()

'  Const bFile = "C:\Test\Backup\Personal.xlsb_"
  Const bfile = "C:\Users\sganuja\AppData\Roaming\Microsoft\Excel\XLSTART\personal.xlsb"
Const pfile = "I:\EXCEL LEARNING\IMPORTANT FOR UPLOAD TO DRIVE\"
  Application.DisplayAlerts = False
  With Workbooks("Personal.xlsb")
    .SaveCopyAs pfile & Format(Now(), "yy_mm_dd_hhmm") & ".xlsb"
    .Save
  End With
  Application.DisplayAlerts = True
End Sub

i just started to study powershell as below.
(1) =youtube]6CRTahGYnws
(2) =youtube]6VK4TN6Umfk

& may be there are more...

Again Thank you very much sir for your effort to resolve..

Regards,

Chirag Raval
 
You must log in or register to reply here.
Back
Top