Opening files

[Daniel]

Hello,
I have the following sub that works great for regular xls files. Now I
need to use the similar sub but to open excel files that has macros. I am
trying to open xls files without having the macro to proceed. Is there a
way to do this by amending the following code? The purpose is to open files
and then consolidate them into one.

Thank you for your help.

Sub openQuarterlyFiles()
Dim selectedFile As String
Dim wbToOpen As Integer, wsCount As Integer

With objWsQBRfreeB
lastline = Range("A32000").End(xlUp).Row
For wbToOpen = 1 To 10
'display dialog asking user to select a file
selectedFile = Application.GetOpenFilename( _
"Files (*.xls),*.xls", , "Select your Monthly Free Balance
file", , False)
'check if cancel selected then open
If selectedFile = "False" Then
MsgBox "You choose to interrupt loading files."
Call closeQBRFreeBalance
Exit For
Else
Workbooks.OpenText fileName:=selectedFile
Call openDelimitedFile(selectedFile)
End If
wsCount = Workbooks(1).Worksheets.count
Workbooks(2).Activate
Sheets(1).Move After:=Workbooks(1).Sheets(wsCount)
Next
End With
processQfiles
End Sub

 

[STEVE BELL]

Am not sure but you may just want to turn off events and alerts.

Application.DisplayAlerts = False ' this will turn off all alerts
' and allow opening
book with macros on
Application.EnableEvents = False ' this will prevent all event macros
from running

' YOUR CODE TO OPEN WORKBOOK

' Reset to "Normal"
Application.DisplayAlerts = True
Application.EnableEvents = True

 

[Tom Ogilvy]

OpenText is used for opening text files yet you say you are opening XLS
files.
You are calling a subroutine with no explanation of what it does.

What do you mean by "without having the macro to proceed. "

Perhaps a little clarity on what your issue is would be helpful.

 

[Daniel]

Is with pleasure. The code listed do open text file even if they bear .xls
files (comma delimited or whatever). I take these files and format them
using a excel addin I created. At the end the new file will be a xls file
containing code in the thisworkbook, two modules and a reference to an
addin.

I would like to consolidate those files and I am stuck with the fact that
the macros of the file I am opening wants to run and I don't need them to be
functional in the consolidation I need to do (but I don't want to remove
them in the original as they are still usefull).

I have tried Steve Bell suggestion, it seems to be working. I find that it
does take a long time to open files (10 seconds for the 1st and 5th file).

Thank you.
__________

 

[Tom Ogilvy]

Yes, Application.EnableEvents does stop the Workbook_Open macro from running
and also stops any other Excel type library events from firing.

 

[STEVE BELL]

Daniel,

Glad it helped.

Are the slow files very large, lots of formulas, have external references,
etc.
If so - you can turn Calculation on/off, Update exterenal references on/off,
etc.

 

[Daniel]

How do I do tell excel not to calculate before opening the file?

Thanks
_______________

 

[STEVE BELL]

Daniel,

wrap your code with this:

Application.Calculation = xlCalculationManual
' your code

Application.Calculation = xlCalculationAutomatic


p.s.

Go to the VBE Editor
Select "Tools" menu
Select "Options"
Select "Editor" tab
Check "Auto List Members"

Now as you write code - most times when you add a "." or a space, or an "="
a dropdown will appear with choices.

Try this by typing Application.
and see what happens...

takes a little practice but it is very helpful...

 

[gearoi]

Hi Chaps

I have a large calculations spreadsheet which needs to open some CSV
files. I have the code sorted to do all that, e.g.

Application.Calculation = xlCalculationManual
Application.EnableEvents = False


Workbooks.Open Filename:= _
"L:\FTP_ROOT\RISK_TST\Reports\rpt_013_liabilities.csv"

etc..


There are about 5 sheets to open and then later close.

MY PROBLEM: Excel calculates the WHOLE CALCULATIONS spreadsheet when I
open the CSV file, even though calculation is off. I can only assume
this is part of the way that I've opened the file.

Please help - it takes FAR too long to recalculate the entire
spreadsheet now and what I do with the rest of the very long macro is
to make it calculate only the sections it needs - ie
range("x").calculate or sheets("y").calculate and so on. This bit works
fine though.

So - how do I make a CSV file open WITHOUT recalculating?

Thanks so much for your help

 

[gearoi]

In case anyone wants to know, I got around this problem rather tha
fixing it - I just had a small pre-calcs spreadsheet that opened th
CSV files and converted them to XLS files. It didn't matter that i
still calculated the whole spreadsheet, because the whole spreadshee
was very small