Opening Excel Workbooks with VBA

I have a form that's opening excel (if it's not already open), opening two seperate workbooks then looping through ranges to copy and paste values from one workbook to the other. This works fine if I don't have another instance of excel open and if I run the code once then open another instance of excel.

Issue: If I have an excel workbook open then run the code, the loop statement doesn't have an end point (loop continues on until accessis blown off).

I've provided an example. To keep this as simple as possible I have stripped out all unnecessary data and provided the three excel files the program is using. To test save all files to your C drive.

Any help here is appreciated. Thank you!
Dump.xlsx
Equity-Pipeline.xlsx
Equity-Template.xlsx
Experts-Test.mdb
twandsAsked:
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.

Jeffrey CoachmanMIS LiasonCommented:
So this is being done through MS Access?

Seems like more of an Excel Question, but you posted it to the Access zone...?

JeffCoachman
0
twandsAuthor Commented:
Yes. I'm opening ExcelObjects but in Access VBA. Should I also post this question under Excel?
0
Jeffrey CoachmanMIS LiasonCommented:
No, I was just wondering if this was an Excel Q or an Access Q.

But it would not hurt to request that the Excel zone be added...

;-)

Jeff
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

dlmilleCommented:
I'm not surprised you're having difficulties, and I would note a few constructive points that should help.

You have a couple key statements:
    Set objWB1 = ExcelObject.Application.Workbooks.Open(sFile2)
    Set objWB2 = ExcelObject.Application.Workbooks.Open(sFile)

but then you start using Windows(excelfile).activate and you start directly reference sheet objects which have no parent, etc.

To control Excel, you must start with the Application Object, then Workbook Object, then Worksheet Object, then Range Object (for example).

So, heres a few statements in your code, but you have not referenced who owns the Cells, who owns the Sheets, etc.
Do Until IsEmpty(Cells(1, iCol))

objWB1.Sheets(1).Activate


    'copying the property name
    'objWB2.Sheets(1).Activate
    Windows("Dump.xlsx").Activate
    Sheets(1).Cells(1, iCol).Select
    Selection.Copy
    'objWB1.Sheets(1).Activate
    Windows("Equity Template.xlsx").Activate
    Sheets(1).Cells(8, iCol2).Select
    Selection.PasteSpecial xlPasteValues

Open in new window


This particular snippet should be written something like:
dim wks1 as Excel.Worksheet
dim wks2 as Excel.Worksheet
dim r as Excel.Range

set wks1= objWB1.Sheets(1)
set wks2 = objWB2.Sheets(1)

Do Until IsEmpty(wks1.Cells(1, iCol))

    'copying the property name
 
    wks2.Cells(1, iCol).Select
    Selection.Copy

    'objWB1.Sheets(1).Activate
    'Windows("Equity Template.xlsx").Activate

'Activate Workbook 1 as follows
'objWB1.Activate '<- this is the correct syntax

    wks1.Cells(8, iCol2).Select
    Selection.PasteSpecial xlPasteValues

Open in new window


Written again, optimized:
Dim wks1 As Excel.Worksheet
Dim wks2 As Excel.Worksheet
Dim r As Excel.Range

Set wks1 = objWB1.Sheets(1)
Set wks2 = objWB2.Sheets(1)

Do Until IsEmpty(wks1.Cells(1, iCol))

    'copying the property name
 
    wks2.Cells(1, iCol).Copy

    wks1.Cells(8, iCol2).PasteSpecial xlPasteValues

Open in new window


So, go through your code and ensure all your variables that reference excel object have ownership from the Application, to the Workbook, Sheet, then Range/Cell.

Hope this helps.

Dave
0
dlmilleCommented:
Here.  I think I got you half-way.  I wish I had renamed objWB1 to objEquity and objWB2 to objDummy as it would have helped, but check my work, and I think from this you should be ablel to finish out the rest ;)

Option Compare Database

Private Sub cmdEquity_Click()
On Error GoTo Err_cmdEquity_Click

    Dim sFile As String
    Dim sFile2 As String
    Dim ExcelObject As Excel.Application
    Dim sNewName As String
    Dim objWB1 As Excel.Workbook
    Dim objWB2 As Excel.Workbook
    Dim objWS1 As Excel.Worksheet
    Dim objWS2 As Excel.Worksheet
    
On Error Resume Next
    ' if excel is already open it uses that instance
    Set ExcelObject = GetObject(, "Excel.Application")

    ' if excel isn't open, it opens a new instance of excel
    If ExcelObject Is Nothing Then
        Set ExcelObject = New Excel.Application
        ExcelObject.EnableEvents = False
    End If
    

    'open excel shell file
    sFile2 = "C:\Equity Template.xlsx"
    sFile = "C:\Dump.xlsx"
    
    
    Set objWB1 = ExcelObject.Application.Workbooks.Open(sFile2) 'equity
    Set objWB2 = ExcelObject.Application.Workbooks.Open(sFile) 'dump
    
   'set the mouse pointer to an hourglass(wheel)
   SavedPointer = Screen.MousePointer
   Screen.MousePointer = 11

Dim iCol As Integer
Dim iCol2 As Integer

iCol = 3
iCol2 = 4

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'copy over all the values to the 0-6 month tab of the template

Set objWS1 = objWB1.Sheets(1) 'equity
Set objWS2 = objWB2.Sheets(1) 'dump

Do Until IsEmpty(objWS2.Cells(1, iCol)) 'must be WB2 - last file opened

objWB1.Sheets(1).Activate


    'copying the property name
    
    objWS2.Cells(1, iCol).Copy
    objWS1.Cells(8, iCol2).PasteSpecial xlPasteValues
    
    'copying the city, state, zip, couty
    objWS2.Range(objWS2.Cells(3, iCol), objWS2.Cells(7, iCol)).Copy
    objWS1.Range(objWS1.Cells(9, iCol2), objWS1.Cells(13, iCol2)).PasteSpecial xlPasteValues
    
    'copying the units
    objWS2.Cells(8, iCol).Copy
    objWS1.Cells(15, iCol2).PasteSpecial xlPasteValues
    
    'copying project type section
    objWS2.Range(objWS2.Cells(9, iCol), objWS2.Cells(12, iCol)).Copy
    objWS1.Range(objWS1.Cells(17, iCol2), objWS1.Cells(20, iCol2)).PasteSpecial xlPasteValues

    'copying contact section
    objWS2.Range(objWS2.Cells(14, iCol), objWS2.Cells(16, iCol)).Copy
    objWS1.Range(objWS1.Cells(22, iCol2), objWS1.Cells(25, iCol2)).PasteSpecial xlPasteValues
    
    'copying tax credit section
    objWS2.Range(objWS2.Cells(18, iCol), objWS2.Cells(27, iCol)).Copy
    objWS1.Range(objWS1.Cells(27, iCol2), objWS1.Cells(36, iCol2)).PasteSpecial xlPasteValues
    
    'copying rehab section
    objWS2.Cells(29, iCol).Copy
    objWS1.Cells(39, iCol2).PasteSpecial xlPasteValues
    
    'copying demographic section
    objWS2.Range(objWS2.Cells(31, iCol), objWS2.Cells(35, iCol)).Copy
    objWS1.Range(objWS1.Cells(42, iCol2), objWS1.Cells(46, iCol2)).PasteSpecial xlPasteValues
    
    'copying subsidy section
    objWS2.Range(objWS2.Cells(37, iCol), objWS2.Cells(41, iCol)).Copy
    objWS1.Range(objWS1.Cells(49, iCol2), objWS1.Cells(53, iCol2)).PasteSpecial xlPasteValues

    'copying project section
    objWS2.Range(objWS2.Cells(43, iCol), objWS2.Cells(51, iCol)).Copy
    objWS1.Range(objWS1.Cells(56, iCol2), objWS1.Cells(64, iCol2)).PasteSpecial xlPasteValues

    iCol = iCol + 1
    iCol2 = iCol2 + 1
    
Loop


'delete all the blank columns at the end of the sheet
iCol2 = 8

    Do Until objWS1.Cells(8, iCol2).Interior.ColorIndex <> 37
    
        objWS1.Columns(iCol2).Delete Shift:=xlLeft
        
    Loop

'convert all the unit totals to numbers
    iCol2 = 4
    Dim Val As String
    
    Do Until IsEmpty(objWS1.Cells(15, iCol2))
    
        objWS1.Cells(15, iCol2).NumberFormat = "General"
        objWS1.Cells(15, iCol2).Value = objWS1.Cells(15, iCol2).Value
        
        'Val = ActiveCell.Value
        'ActiveCell.Value = Val
        
        iCol2 = iCol2 + 1
        
    Loop
'convert all the tax credit alloc cells to numbers
    iCol2 = 4
    Do Until IsEmpty(objWS1.Cells(28, iCol2))
    
        objWS1.Cells(28, iCol2).NumberFormat = "General"
        objWS1.Cells(28, iCol2).Value = objWS1.Cells(28, iCol2).Value
        
        'Val = ActiveCell.Value
        'ActiveCell.Value = Val
        
        iCol2 = iCol2 + 1
        
    Loop
    
'ExcelObject.Application.Workbooks("Dump.xlsx").Close
objWS2.Close savechanges:=False

Open in new window


Don't hesitate to post if you need additional help.

Cheers,

Dave
0

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
twandsAuthor Commented:
YES!! Thank you, this works perfectly!
0
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 Access

From novice to tech pro — start learning today.