Results 1 to 3 of 3

Set a variable as ActiveCell in Excel VBA

Threaded View

  1. #1
    Registered User
    Join Date
    07-23-2019
    Location
    TW
    MS-Off Ver
    2016
    Posts
    12

    Set a variable as ActiveCell in Excel VBA

    "SOLVED"

    I am copying tables from a word .docx file into an excel sheet.

    My code below works fine, however, my issue is that I want to set my resultRow variable to be the ActiveCell.row instead of a fixed integer. So that I can paste my table into an active cell rather than a defined cell range. I have tried changing it to resultRow = ActiveCell and Set resultRow = ActiveCell but they are not working. Any help is appreciated. My code is shown below:

    Sub ImportWordTable()
    
    Dim wdDoc As Object
    Dim wdFileName As Variant
    Dim tableNo As Integer 'table number in Word
    Dim iRow As Long 'row index in Excel
    Dim iCol As Integer 'column index in Excel
    Dim resultRow As Long
    Dim tableStart As Integer
    Dim tableTot As Integer
    
    On Error Resume Next
    
    ActiveSheet.Range("A:AZ").ClearContents
    
    wdFileName = Application.GetOpenFilename("Word files (*.docx),*.doc", , _
    "Browse for file containing table to be imported")
    
    If wdFileName = False Then Exit Sub '(user cancelled import file browser)
    
    Set wdDoc = GetObject(wdFileName) 'open Word file
    
    With wdDoc
        tableNo = wdDoc.Tables.Count
        tableTot = wdDoc.Tables.Count
        If tableNo = 0 Then
            MsgBox "This document contains no tables", _
            vbExclamation, "Import Word Table"
        ElseIf tableNo > 1 Then
            tableNo = InputBox("This Word document contains " & tableNo & " tables." & vbCrLf & _
            "Enter the table to start from", "Import Word Table", "1")
        End If
    
        resultRow = 1
    
    For tableStart = tableNo To tableTot
        .Tables(tableStart).Borders.Enable = True
        .Tables(tableStart).Range.Copy
        ActiveSheet.Range("A" & resultRow).PasteSpecial Paste:=xlPasteAll
        resultRow = ActiveSheet.Range("A" & ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row).End(xlUp).Row + 1
    Next
    ActiveSheet.Range("A1:AZ" & resultRow).UnMerge
    End With
    
    End Sub
    Last edited by AAS44; 05-22-2020 at 02:58 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Variable in ActiveCell.FormulaR1C1?
    By russwongg in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-18-2016, 10:56 PM
  2. [SOLVED] Variable for sheet name in vba ActiveCell.Formula
    By bstier in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-29-2015, 10:49 PM
  3. Selecting a range of cells using ActiveCell where the start point is variable
    By jshaw82 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-12-2013, 05:54 AM
  4. Output contents of Activecell when Activecell may be string or numeric.
    By jfriddle in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-09-2012, 12:27 PM
  5. Activecell offset with Variable
    By sgreni in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-04-2011, 05:42 PM
  6. Countif with activecell.row variable for range?
    By JP Romano in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-24-2011, 11:45 AM
  7. How to use ActiveCell Adress in variable?
    By Castor77 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-11-2010, 11:28 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1