• 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.

Converting files to values

juanito

juanito

Member
I have a folder with a lot of Excel files, of the coporate year-end reporting sort. I need to convert all of them to values. Do I really have to do this workbook by workbook, sheet by sheet? Any shortcuts please?
 
"convert to values"


Please define this better. Are you wanting all formulas to become values? Are you wanting a grand sum?
 
Hi Luke, I want all the formulas to convert to values, that's all. Could do it with paste special but it's a lot of workbooks with a lot of sheets - there must be a more efficient way. Thanks.
 
You can write a macro to open each of the workbooks one by one


Loop through all the sheet and copy paste values in them


and the save and close (all through the macro)


~Vijay
 
Thanks Vijay - that's probably how it'll need to be done. I don't have much macro experience but that's my problem now!

John
 
Here you go... part of this is adapated from Hui's code


Code: 
Sub List_Files()

Dim f As Object, fso As Object

Dim folder As String

Dim wb As Workbook, ws As Worksheet

Set wb = ActiveWorkbook

Set ws = ActiveSheet

Set fso = CreateObject("Scripting.FileSystemObject")

With Application.FileDialog(msoFileDialogFolderPicker)

.Show

If .SelectedItems.Count = 0 Then

MsgBox "Cancelled"

End

End If

folder = .SelectedItems(1)

End With


    For Each f In fso.GetFolder(folder).Files

If Right(f.Name, 3) = "xls" Then

Application.Workbooks.Open f.Name

Application.DisplayAlerts = False

For Each sht In ActiveWorkbook.Sheets

sht.Select

Cells.Select

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Range("A1").Select

Next

ActiveWorkbook.Close True

End If


    Next

End Sub
 
Sorry forgot to explain...


After you run the macro... you will be presented with a open dialog box...


Just locate the folder which has all your excel files and click on OK


Rest the code will do automatically...


PS: copy paste as values is an irreversible process; please ensure you have made backups before you run the macro


~Vijay
 
Vijay - I'm not a VBA user so I've probably made some beginner's errors. I set up a trial folder to test your code and had a few failures and one success - without being able to see what I'd done right. Now I'm consistently failing!


I opened a new workbook, opened VBA and copied your code, then pressed F5 and selected the folder. Same each time.


I'm running Excel in Spanish but that isn't a problem with VBA, right?


Thanks
 
Juanito,


Spanish should not be a problem... could you advise what version of excel you are using...


and the files that need to be pasted as values what is the version for them.


In the above code; we are only looking for files that have XLS extension (v2003 and previous)


~Vijay
 
Yes, that might be it then. I'm running 2007, but I've only just upgraded from 2003 so I'll have a mix of xls and xlsx to convert. I looked at your code and did wonder if that could have something to do with it. If you could tell me the change I need to make to the VBA that'd be great - meanwhile I'll check that the code works if I just use xls files


Thanks
 
John,


Do you have teamviewer installed on your system, we can have a remote session where i can check and advice on what is happening.


I am also on FB.... http://www.facebook.com/profile.php?id=643050427


~Vijay
 
Haven't got teamviewer (I have got gotomeeting though). I've sent a screenshot to your facebook address. Thanks
 
Great...


Can you please set a gotomeeting and send me the invite on sharma.vijay1-at-gmail.com


I will join in


~Vijay
 
Vijay - it turns out that for an unrelated issue I was recently (but before I migrated to v2007) told to save the personal.xls file to the following address in my PC:


C:Documents and Settings"juanito"Application DataMicrosoftExcelXLSTART


Now is that the wrong place and could that be causing the issues? It's not the same place that Hui said here (http://chandoo.org/forums/topic/how-can-i-reuse-macros#post-4432) but even so he warned that locations can vary.
 
John,


Please see the full code below


{code}

Sub List_Files()

Dim f As Object, fso As Object

Dim folder As String

Dim wb As Workbook, ws As Worksheet

Set wb = ActiveWorkbook

Set ws = ActiveSheet

Set fso = CreateObject("Scripting.FileSystemObject")

With Application.FileDialog(msoFileDialogFolderPicker)

.Show

If .SelectedItems.Count = 0 Then

MsgBox "Cancelled"

End

End If

folder = .SelectedItems(1)

End With


For Each f In fso.GetFolder(folder).Files

If Right(f.Name, 3) = "xls" Or Right(f.Name, 4) = "xlsx" Then

Application.Workbooks.Open f.Name

Application.DisplayAlerts = False

For Each sht In ActiveWorkbook.Sheets

sht.Select

Cells.Select

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Range("A1").Select

Next

ActiveWorkbook.Close True

End If


Next

End Sub

{/code}


~Vijay
 
Juanito

Start recording a new macro useing the Record Macro button on the developer Dialog

Change the "Store macro in:" to Personnel.xlsx

do something trivial and stop recording


Now go and look for your personnel.xlsx file which will have a new Date/Time


Shift your macros as appropriate
 
Hui: I wasn't able to specify the name of "personnel.xlsx" (did you mean "personal") since it was saved by default. However, I saved the trivial macro as you suggested and it's "personal.xls" at the address I show a couple of posts above. I suspect that wasn't the issue and that we're good now.

Thanks!
 
You must log in or register to reply here.
Back
Top