Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Copy file help

Posted on 2011-02-15
5
Medium Priority
?
338 Views
Last Modified: 2012-06-22
Please can you help:-

Sheets(Array("Low Volume", "DataSheet")).Copy

With the attached code it saves a copy of sheets 4 & 6. The trouble is that within the copied file, the launch userform no longer works.

When I open the file the Forms module is not copied.

Please can you help alter the code so that the entire workbook is copied.

Private Sub CommandButton2_Click()
Dim strPath As String, strFileName As String
Dim Extn As String, strFname As String
Dim INUm As Long, CheckMe As Long

strPath = "C:\Documents and Settings\Administrator\My Documents\Gary\project\New Folder\"
strFileName = "Summary_" & Format(Sheets("Low Volume").Range("C5").Value, "YYYY-MM-DD")
Extn = ".xls"

strFname = Dir(strPath & strFileName & "_" & INUm & Extn)
Do While Len(strFname) <> 0
INUm = INUm + 1
strFname = Dir(strPath & strFileName & "_" & INUm & Extn)
Loop

Sheets(Array("Low Volume", "DataSheet")).Copy
ActiveWorkbook.SaveAs Filename:=strPath & strFileName & "_" & INUm & Extn
ActiveWorkbook.Close
CheckMe = MsgBox("Clear Contents?", vbYesNo)
If CheckMe = vbYes Then

Sheets("Low Volume").Unprotect Sheets("DataSheet").Range("B2").Value
ThisWorkbook.ActiveSheet.Range("B14:K414,W14:W414,N14:N414,Q14:Q414").ClearContents
Sheets("Low Volume").Protect Sheets("DataSheet").Range("B2").Value
 End If
End Sub

Open in new window

gary-test3.xls
0
Comment
Question by:Gazza83
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 50

Expert Comment

by:Dave Brett
ID: 34902375
Gary,

As you are effectively copying the entireworkbook I suggest you do a SaveAs rather than a Copy, then resave the file with its original path

This is a lot cleaner than writing code to programmatically copy the UserForm and Module code

The only caution is whether you don't want to save the model at this time

Cheers

Dave
Private Sub CommandButton2_Click()
    Dim strPath As String, strFileName As String
    Dim Extn As String, strFname As String
    Dim INUm As Long, CheckMe As Long
    Dim strThisPath As String
    strThisPath = ThisWorkbook.FullName


    strPath = "C:\Documents and Settings\Administrator\My Documents\Gary\project\New Folder\"
    strFileName = "Summary_" & Format(Sheets("Low Volume").Range("C5").Value, "YYYY-MM-DD")
    Extn = ".xls"

    strFname = Dir(strPath & strFileName & "_" & INUm & Extn)
    Do While Len(strFname) <> 0
        INUm = INUm + 1
        strFname = Dir(strPath & strFileName & "_" & INUm & Extn)
    Loop

    ActiveWorkbook.SaveAs Filename:=strPath & strFileName & "_" & INUm & Extn
    CheckMe = MsgBox("Clear Contents?", vbYesNo)
    If CheckMe = vbYes Then
        Sheets("Low Volume").Unprotect Sheets("DataSheet").Range("B2").Value
        ThisWorkbook.ActiveSheet.Range("B14:K414,W14:W414,N14:N414,Q14:Q414").ClearContents
        Sheets("Low Volume").Protect Sheets("DataSheet").Range("B2").Value
    End If
    ActiveWorkbook.SaveAs strThisPath
End Sub

Open in new window

0
 

Author Comment

by:Gazza83
ID: 34902440
I want the 'orginal file' to retain its own file name. So the 'Archive' file would be separate because it will have a different file name.

If there is a way to SaveAs without unpdating the name of the currently opened workbook then that would be just what  I need.
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 34902470
> I want the 'orginal file' to retain its own file name. So the 'Archive' file would be separate because it will have a different file name

Thats what my code does
ie saves the archive file
then resaves the 'orginal file' with the original name ( ActiveWorkbook.SaveAs strThisPath)

Cheers

Dave


0
 

Author Comment

by:Gazza83
ID: 34902555
Ah I see now, it might be just my pc, but I get a command prompt as whether to replace the existing file.

is there a way to disable this? Or to add "_1" to the file name
save-as.JPG
0
 
LVL 50

Accepted Solution

by:
Dave Brett earned 2000 total points
ID: 34902576
hth

Dave
Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs strThisPath
    Application.DisplayAlerts = True

Open in new window

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question