Opening files
 

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

Opening files
 

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

--
steveB

Remove "AYN" from email to respond
"Daniel" wrote in message
...
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

Opening files
 

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.

--
Regards,
Tom Ogilvy

"Daniel" wrote in message
...
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

Opening files
 

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" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Daniel" wrote in message
...
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

Opening files
 

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

--
Regards,
Tom Ogilvy

"Daniel" wrote in message
...
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" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Daniel" wrote in message
...
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

Opening files
 

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.

--
steveB

Remove "AYN" from email to respond
"Daniel" wrote in message
...
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" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Daniel" wrote in message
...
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

Opening files
 

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

Thanks
_______________
"STEVE BELL" wrote in message
news:u1Wxe.5494$Yb4.110@trnddc08...
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.

--
steveB

Remove "AYN" from email to respond
"Daniel" wrote in message
...
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" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Daniel" wrote in message
...
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

Opening files
 

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

Remove "AYN" from email to respond
"Daniel" wrote in message
...
How do I do tell excel not to calculate before opening the file?

Thanks
_______________
"STEVE BELL" wrote in message
news:u1Wxe.5494$Yb4.110@trnddc08...
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.

--
steveB

Remove "AYN" from email to respond
"Daniel" wrote in message
...
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" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Daniel" wrote in message
...
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

Opening files
 

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
------------------------------------------------------------------------
gearoi's Profile: http://www.excelforum.com/member.php...o&userid=26576
View this thread: http://www.excelforum.com/showthread...hreadid=384178

Opening files
 

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

--
gearo
-----------------------------------------------------------------------
gearoi's Profile: http://www.excelforum.com/member.php...fo&userid=2657
View this thread: http://www.excelforum.com/showthread.php?threadid=38417