CODEWITHSUNDEEP
excelvba
Serena
Serena

Reputation: 11

Is it possible to create a new workbook that is not active?

Updated issue: The real issue seems to be my use of appending a date to the file name. I can't seem to reformat the date, and the error seems to be triggered by / in the date, so how can reformat this so excel understands that the date is included in the file name? I understand now that SaveCopyAs does not need a "dummy" file to upload to first.

I'm currently getting a runtime error 1004 for the method SaveCopyAs saying that it can't find the location of the new file I'm trying to create. I interpreted this to mean that I need to create a "dummy" file to hold the copied information before I overwrite it with the data and a new file name. Is this correct?

The solution would be simple to create the dummy file and refer to each file individually, and then continue as I interpreted above. However, I'm trying to loop this process and am using a module that is hosted in PERSONAL.XLSB so I cannot use ThisWorkbook and have to use ActiveWorkbook. My issue now is that I don't know how to create a automated process of copying the data to a new file because of my use of ActiveWorkbook.

This is my current code:

Sub cellvalue_filename()

    Dim Path
    Dim NewPath
    Dim WBname
    Dim Destination

    Path = "C:\oldfilelocation\"
    WBname = ActiveWorkbook.Name
    NewPath = "C:\newfilelocation\"

    Destination = NewPath & Range("B2") & WBname

    ActiveWorkbook.SaveCopyAs filename:=Destination
    ActiveWorkbook.Close

End Sub

Upvotes: 1

Views: 301

Answers (1)

to StackOverflow
to StackOverflow

Reputation: 124776

As a general rule, it's better in VBA to refer explicitly to the workbook and worksheet you want, rather than relying on it being active.

You say you can't use ThisWorkbook because you are referencing a workbook other than the one that contains your VBA code, but that doesn't mean you have to use ActiveWorkbook.

Instead you can get a reference to a workbook when you open or create it, e.g.

Dim objWorkbook As Workbook
Set objWorkbook = Application.Workbooks.Open(...)
...
objWorkbook.SaveCopyAs ...

Similarly using Range("B2") without specifying a Worksheet will assume the active worksheet in the active workbook. Instead, explicitly specify the worksheet, e.g.:

Dim objWorksheet As Worksheet
Set objWorksheet = objWorkbook.Worksheets(1) ' or whatever worksheet you want 
...
Destination = NewPath & objWorksheet.Range("B2") & WBname

Upvotes: 3

Related Questions