Howto: Open an excell file in vba

[xml2]

Question is simple (see thread title). I'll explain what I've tried and what it should be for.

We have a *big* excell file which contains a lot of external data. (+10.000 fields)

What I want to do is make a copy of the file with only the values in it. Otherwise our users can not use the file because everytime you change something the computers need aprox. 10 to 15 minutes to calculate it..

Does anybody have a better sollution, or could you give me the code snippet for opening an excell file within a macro..

 

[RamziSaab]

did u know u can go to tools-->options-->calculattion tab-->
and choose manual calculations

This will tell excel not to do any calculations until the user presses the F9 key.

To open a file, you can always record the macro and it will tell you how it did it.

Hope this helps

Ramzi

 

[xml2]

I've done the recording thingie, but it doesn't provide with any kind of code. But I'd be suprised if it wasn't possible in excel, because other file operations are possible.

quote:
did u know u can go to tools-->options-->calculattion tab-->
and choose manual calculations

The problem is that this need to be done for every client pc and it counts for all the other excel files to, so this is not an option.

thx for the help dough, maybe someone else?

 

[RamziSaab]

Workbooks.Open Filename:= _
"C:\mypath\filename.xls"

this will open a file using a macro

 

[DrBowes]

Workbooks.Open Filename:= _
"C:\mypath\filename.xls", _
UpdateLinks:= 0

to prevent external links from being updated.

 

[xml2]

Thx, the code looks like this now to copy and paste the values but I get a strange error. When I do it with the macro recording function it works dough.. (code is commented in dutch)

Sub sheet_snapshot()

' originele worksheet openen
Workbooks.Open Filename:= _
"Y:\Scan\OUT\old\021205pr productie-overzicht 2002 nieuw2.xls", _
UpdateLinks:=0

' cellen kopieren
Cells.Select
Selection.Copy

' Waarden plakken
ActiveWindow.ActivateNext
Cells.Select
Selection.ClearContents
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

' Opmaak toepassen
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


End Sub

 

[Jooky68]

what is the error that you are getting?? In english if possible please

 

[xml2]

Hehe, I'll translate it

Fault 1004 while executing:
Method PasteSpecial of class Range failed


(at the point when I'm trying to paste)

' Waarden plakken
ActiveWindow.ActivateNext
Cells.Select
Selection.ClearContents
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ <<--- HERE
:=False, Transpose:=False

 

[RamziSaab]

your problems is in the Selection.ClearContents
to fix that clear contents in that sheet first, then copy and paste...when u are clearing contents u are resetting the copy (i.e. u have copied nothing)