[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1500
  • Last Modified:

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
0
adhalabh
Asked:
adhalabh
  • 3
  • 2
  • 2
1 Solution
 
[ 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 ]
0
 
adhalabhAuthor Commented:
They are the Path to and name of an excel file to be opened
0
 
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
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
[ 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*?
0
 
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.
0
 
bruintjeCommented:
you mean something like

set wbk2 = workbooks.open "etc"

?
0
 
[ fanpages ]IT Services ConsultantCommented:
Apparently so, Brian :)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now