Copying file

[Roadhouse]

Hi has anyone had this problem?

I'm trying to copy the excel workbook files while they are linked to gruss I have 8 workbooks A.xlsm to H1.xlsm linked to 8 tabs but when it is linked to gruss it only copies the workbook on the first tab A1.xlsm and not the others.

If I unhook all 8 workbooks all 8 get copied fine can anyone help with this ?

[osknows]

Assuming you mean copying the file (rather than copying the contents of a sheet) you can use SaveCopyAs so that the original remains allowing it to stay connected to Gruss.

Code: Select all

Option Explicit

Sub test()
Dim i As Long
Dim wbArray(1 To 7) As Workbook
Dim fileName As String

    'Assume workbook filenames are Workbook1.xlsm to Workbook8.xlsm

    'Load workbooks to Array
    For i = LBound(wbArray, 1) To UBound(wbArray, 1)
        fileName = Replace("C:\Users\o\Desktop\New folder\Workbook{0}.xlsm", "{0}", i)
        Set wbArray(i) = GetObject(fileName)
    Next i
   
    'Save copy of workbooks with timestamp
    For i = LBound(wbArray, 1) To UBound(wbArray, 1)
        fileName = Replace(wbArray(i).FullName, ".", "_" & Format(Now, "yyyymmdd") & ".")
        wbArray(i).SaveCopyAs (fileName)
    Next i
   
'    'CleanUp if needed
'    For i = LBound(wbArray, 1) To UBound(wbArray, 1)
'        wbArray(i).Close
'        Set wbArray(i) = Nothing
'    Next i
   
End Sub

[Roadhouse]

Hi thanks Osknows for your help, I put the code into a module set up workbook1 and workbook2 to gruss but the code highlighted getobject workbook name as an error?

[osknows]

Have you changed the path?

This is specific to me: "C:\Users\o\Desktop\New folder\Workbook{0}.xlsm"

[Roadhouse]

I made it this

Code: Select all

fileName = Replace("C:\Users\Administrator\Downloads\New folder\Workbook{0}.xlsm", "{0}", i)

[osknows]

What is the exact error? I just hooked up 8 sheets separately to Gruss and it worked okay this end.

[Roadhouse]

hi Os it says runtime error 432 and highlights Set wbArray(i) = GetObject(fileName)

[osknows]

Have you changed

Dim wbArray(1 To 7) As Workbook
to
Dim wbArray(1 To 2) As Workbook

to only load two workbooks instead of 7?

[Roadhouse]

hi I changed it to two workbooks and same error came up

[osknows]

The only thing that can cause error 432 is an incorrect filename or path https://msdn.microsoft.com/en-us/librar ... 09(v=vs.60).aspx

Double check the path is correct. Right click on the file and open properties and check the full path and filename with extension. Then put a breakpoint on line Set wbArray(i) = GetObject(fileName) and when the code stops check the above line to see what values the filepath is held in the variable fileName.

[osknows]

Eg, are your files .xls? The current code is checking for .xlsm

[Roadhouse]

No they are xlsm, I put workbook1 and workbook2 and the worksheet with the copy code in the same file downloads

[Roadhouse]

Hi Osknows I've got it to work thanks for your help