UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Getting An "object Variable Or With Block Variable Not Set" Error Message, Office 2010    
 
   
Jrayhan
post May 22 2019, 11:58 AM
Post#1



Posts: 620
Joined: 29-November 12



Hello UA!
I am building an Access to Excel transfer process and applied some formatting to it. I have done the coding in Access database and keep getting an error message. Image below.
I can't seem to find a solution for it. I have included my coding below. Hoping someone can point out the issue here.

Thanks in advance!

-Ray

CODE
Private Sub cmdTaxonomy_Click()
On Error GoTo SubError

    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet

    xlfile = "\\usnym\Data\Taxonomy Report\Taxonomy.xlsx"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Taxonomy_For_MW_Final", xlfile, False


    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Open(xlfile)
    xlApp.Visible = True

    Set xlSheet = xlBook.Worksheets(1)
  
    With xlSheet
        .Name = "Taxonomy Report"
        .Cells.Font.Name = "Calibri"
        .Cells.Font.Size = 11

        .Columns("A").ColumnWidth = 14
        .Columns("B").ColumnWidth = 37
        
        .Range("A1:B1").Select
        Selection.Font.Bold = True   <----------------------- [THIS IS WHERE THE ERROR MESSAGE KEEP SHOWING UP]
    
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent3
            .TintAndShade = 0.599993896298105
            .PatternTintAndShade = 0
        End With
    .Range("C1").Select
     ActiveCell.FormulaR1C1 = "String"
    .Range("C2").Select
     ActiveCell.FormulaR1C1 = "=LEN(RC[-2])"
    .Range("C2").Select
     Selection.AutoFill Destination:=Range("C2:C8074")
    .Range("C2:C8074").Select
    .Range("C1").Select
        Selection.AutoFilter
        ActiveSheet.Range("$A$1:$C$8074").AutoFilter Field:=3, Criteria1:="3"
    .Range("A2").Select
    .Range(Selection, Selection.End(xlToRight)).Select
    .Range(Selection, Selection.End(xlDown)).Select
     Selection.Font.Bold = True
        With Selection.Font
            .Color = -16776961
            .TintAndShade = 0
        End With
    .Range("A1").Select
     Selection.AutoFilter
    .Columns("C:C").Select
     Selection.Delete Shift:=xlToLeft
    .Range("A1").Select
    
    End With
    
SubExit:
On Error Resume Next

xlApp.Visible = True
     Set xlApp = Nothing
    Set xlBook = Nothing
    Set xlSheet = Nothing

    Exit Sub
  
SubError:
    MsgBox "Error Number: " & Err.Number & "= " & Err.Description, vbCritical + vbOKOnly, _
        "An error occurred"
    GoTo SubExit

End Sub

Attached File(s)
Attached File  Error.jpg ( 10.8K )Number of downloads: 0
 
Go to the top of the page
 
DanielPineault
post May 22 2019, 12:00 PM
Post#2


UtterAccess VIP
Posts: 6,666
Joined: 30-June 11



Access knows nothing about Excel and thus knows not what is 'Selection'.

Also, there is no need to even use Selection in such a context, simply
CODE
.Range("A1:B1").Font.Bold = True

Selection slows down the processing and in 99% of cases it can be eliminated, and should! You should consider applying ScreenUpdate to False at the start of your code and then setting it back to True once it is done, this too will help with performance.

--------------------
Daniel Pineault (2010-2018 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
Jrayhan
post May 22 2019, 12:32 PM
Post#3



Posts: 620
Joined: 29-November 12



Thank you for pointing it out!
I have used the Macro Record function in Excel and used the result in Access VBA which seems to cause the issue. I have used your suggestion for the Select, but how can I use AutoFill in Access. Is there are way to autofill the formula down to the end?

CODE
.Range("C2").AutoFill Destination:=Range("C2:C8074")
Go to the top of the page
 
Debaser
post May 22 2019, 01:57 PM
Post#4



Posts: 122
Joined: 11-October 18



Do you have a reference set to the Excel object library? If not, you'll also need to declare the constants you are using like xlThemeColorAccent3 etc.
Go to the top of the page
 
Jrayhan
post May 22 2019, 03:09 PM
Post#5



Posts: 620
Joined: 29-November 12



Yes, I do.
I have decided to do all the formula work in the access query and simply transfer to the excel report. That worked.

Thanks for all your help!! smile.gif
Go to the top of the page
 
Debaser
post May 22 2019, 03:39 PM
Post#6



Posts: 122
Joined: 11-October 18



FWIW, something like this should work:

CODE
Private Sub cmdTaxonomy_Click()
On Error GoTo SubError

    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet

    xlfile = "\\usnym\Data\Taxonomy Report\Taxonomy.xlsx"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Taxonomy_For_MW_Final", xlfile, False


    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Open(xlfile)
    xlApp.Visible = True

    Set xlSheet = xlBook.Worksheets(1)
  
    With xlSheet
        .Name = "Taxonomy Report"
        .Cells.Font.Name = "Calibri"
        .Cells.Font.Size = 11

        .Columns("A").ColumnWidth = 14
        .Columns("B").ColumnWidth = 37
        
        With .Range("A1:B1")
            .Font.Bold = True
            With .Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorAccent3
                .TintAndShade = 0.599993896298105
                .PatternTintAndShade = 0
            End With
        End With
        .Range("C1").Value = "String"
        .Range("C2").FormulaR1C1 = "=LEN(RC[-2])"
        .Range("C2").AutoFill Destination:=.Range("C2:C8074")
    
        With .Range("$A$1:$C$8074")
            .AutoFilter Field:=3, Criteria1:="3"
            With .Font
                .Bold = True
                .Color = -16776961
                .TintAndShade = 0
            End With
            .AutoFilter
        End With
        .Columns("C:C").Delete Shift:=xlToLeft
        .Range("A1").Select
    
    End With
    
SubExit:
On Error Resume Next

xlApp.Visible = True
     Set xlApp = Nothing
    Set xlBook = Nothing
    Set xlSheet = Nothing

    Exit Sub
  
SubError:
    MsgBox "Error Number: " & Err.Number & "= " & Err.Description, vbCritical + vbOKOnly, _
        "An error occurred"
    Resume SubExit

End Sub
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th June 2019 - 04:18 PM