We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Workbooks.Open hangs in excel

adhalabh
adhalabh asked
on
Medium Priority
1,785 Views
Last Modified: 2008-02-07
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
Comment
Watch Question

[ fanpages ]Consultant (Development Team Lead, Senior Support Engineer, and Technical Test Leader)
CERTIFIED EXPERT

Commented:
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 ]

Author

Commented:
They are the Path to and name of an excel file to be opened
Brian MulderFreelance
Top Expert 2006

Commented:
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
[ fanpages ]Consultant (Development Team Lead, Senior Support Engineer, and Technical Test Leader)
CERTIFIED EXPERT

Commented:
>> They are the Path to and name of an excel file to be opened

Yes, I know.

What are the *values*?

Author

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.
Freelance
Top Expert 2006
Commented:
you mean something like

set wbk2 = workbooks.open "etc"

?

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
[ fanpages ]Consultant (Development Team Lead, Senior Support Engineer, and Technical Test Leader)
CERTIFIED EXPERT

Commented:
Apparently so, Brian :)
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.