Workbooks.Open hangs in excel

Hi ,

I have written some code (below) to open an excel workbook and copy a certain column across to another work book. The code hangs at the Workbooks.Open statement.

Does anyone have any idea how to get around the issue?

Private Sub CommandButton1_Click()

'Definition of Variables

Dim AnalyPath As String 'Path to Analyser file
Dim AnalyFile As String 'Name of Analyser file
Dim AnalComp As String 'Complete name of file to open
Dim DateCalc As Date 'Date of Analyser & Static Files
Dim ThetaColm As Long 'Analyser file columnholder for Theta Values
Dim ThetaColm2 As Long 'Analyser file columnholder for ExplainStart Values
Dim ThetaColm3 As Long 'Analyser file columnholder for TCN Values
Dim ThetaColm4 As Long 'Static file columnholder for TCN Values
Dim ThetaColm5 As String 'Static file columnholder for TRADE_TYPE Values
Dim ThetaColm6 As String 'Static file columnholder for MATURITY_DATE Values
Dim StaticPath As String 'Path to Analyser file
Dim StaticFile As String 'Name of Analyser file
Dim StaticComp As String 'Complete name of file to open
Dim n As Integer 'Integer for loop
Dim m As Integer 'Integer for loop
Dim p As Integer 'Integer for loop
Dim q As Integer 'Integer for loop
Dim r As Integer 'Integer for loo
Dim s As Integer 'Integer for loop
Dim t As Integer 'Integer for loop
Dim u As Integer 'Integer for loop
Dim v As Integer 'Integer for loop
Dim w As String '3M,6M,1Y etc...... holder
Dim j As Integer 'Integer for loop
Dim curCell As Variant 'place holder for loop

Set myWkBk = ActiveWorkbook 'Make ThetaByTCN.xls the active workbook

'Get the name and path to the Analyser file from which data is to be extracted from the TCNbyTheta worksheet and confirm it
AnalyPath = Sheets("TCNbyTheta").Range("AnalyserPath").Value
AnalyFile = Sheets("TCNbyTheta").Range("AnalyserFile").Value
DateCalc = Sheets("TCNbyTheta").Range("Date").Value
AnalComp = AnalyPath & "\" & AnalyFile
intMsg = MsgBox("The ANALYSER file to be read (including its path) is" & " " & AnalComp)
StaticPath = Sheets("TCNbyTheta").Range("StaticPath").Value
StaticFile = Sheets("TCNbyTheta").Range("StaticFile").Value
StaticComp = StaticPath & "\" & StaticFile
intMsg = MsgBox("The STATIC file to be read (including its path) is" & " " & StaticComp)

'Open the specified Analyser file find its Theta, ExplainStart and TCN Columns and copy them into the spreadsheet Analysis

n = 0
Do
Workbooks.Open filename:=AnalComp
ActiveSheet.UsedRange.Cells(1, 1).Select
    Do Until ActiveCell.Value = "THETA"
        ActiveCell.Offset(0, 1).Select
    Loop
    ThetaColm = ActiveCell.EntireColumn.Cells.Copy
    Application.DisplayAlerts = False
Workbooks(AnalyFile).Close
myWkBk.Activate
myWkBk.Sheets("Analysis").Cells(1, 1).PasteSpecial Paste:=xlPasteText
n = n + 1
Loop Until n = 1
adhalabhAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
What are the values in these two variables at the point (just before) the code hangs?

AnalyPath = Sheets("TCNbyTheta").Range("AnalyserPath").Value
AnalyFile = Sheets("TCNbyTheta").Range("AnalyserFile").Value

BFN,

fp.
[ http://www.justgiving.com/100RoundsOfSparring ]
adhalabhAuthor Commented:
They are the Path to and name of an excel file to be opened
bruintjeCommented:
Hello adhalabh,

can you use that path and filename as they are in your variables? put them in the run box and open the file or not? could be a drive not available or a mapping missing

bruintje
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

[ fanpages ]IT Services ConsultantCommented:
>> They are the Path to and name of an excel file to be opened

Yes, I know.

What are the *values*?
adhalabhAuthor Commented:
Hi found the solution on a web page through google.

using set once the work book is open resolves the issue.

Thanks for your help.
bruintjeCommented:
you mean something like

set wbk2 = workbooks.open "etc"

?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
[ fanpages ]IT Services ConsultantCommented:
Apparently so, Brian :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.