MS Access VBA format Excel

Question

Hi I am trying to format an excel spread sheet created by my MS access macro. I wanted to select rows with only values in it. So for example I want to select the first row and text wrap it

I thought this logic would work, but gives me error 1004 (Application-defined or Object defined Error)

Dim my_xl_app As Object
Dim my_xl_workbook As Object

Set my_xl_app = CreateObject("Excel.Application")
Set my_xl_workbook = my_xl_app.Workbooks.Open(C:\PATH)

For x = 1 To 23
my_xl_workbook.sheets(x).Range("A1",my_xl_workbook.sheets(x).Range("A1").End(xlToright)).WrapText = True
Next x

my_xl_workbook.Sheets(x).Range("A1", my_xl_workbook.Sheets(x).Range("A1").End(xlToRight)).WrapTex‌t = True is what is being highlighted when I press debug

Thanks in advance

Your For loop is looping through Worksheets my_xl_workbook.sheets(x) so unless you have 23 worksheets it will through an error 1004 because the worksheet you're trying to select doesn't exist. I think from your question you're attempting to loop through the rows on a worksheet. — Gordon
– Gordon, Commented Feb 27, 2017 at 15:14
@gordon Hi Thank your for the answer, I have 23 worksheets that I am looping through, and I want to format each of the top rows I originally had my_xl_workbook.sheets(x).RANGE("A1:AB1").WrapText = True which worked but then I realized that not all my sheets were same length so when I ran my next formatting code my_xl_workbook.sheets(x).RANGE("A1:AB1").Autofilter it filtered more than I needed on some spreadsheets. — Oxy111
– Oxy111, Commented Feb 27, 2017 at 15:20

Vityata · Accepted Answer · 2017-02-27 16:16:10Z

1

You are probably not closing properly the file, thus it stays opened and unvisible. Check in your task manager how many excel files do you have opened. Try to close them all. Furthermore, you refer to xlToRight, which is member of the MS Excel Object Library, which is not present in your application.

Thus, try the following:

Public Sub TestMe()

    Dim x               As Long
    Dim my_xl_app       As Object
    Dim my_xl_workbook  As Object

    Set my_xl_app = CreateObject("Excel.Application")
    Set my_xl_workbook = my_xl_app.Workbooks.Open("C:\Users\v.doynov\Desktop\file.xlsx")

    my_xl_app.Visible = True

    For x = 1 To my_xl_workbook.Sheets.Count
        With my_xl_workbook.Sheets(x)
            .Range("A1", .Range("A1").End(xlToRight)).WrapText = True

            Debug.Print "Wrapping " & .Range("A1", .Range("A1").End(-4161)).Address & _
            " From " & .Range("A1", .Range("A1").End(-4161)).Parent.Name

        End With
    Next x

    my_xl_workbook.Save
    my_xl_workbook.Close (True)

End Sub

This is how I found -4161. Add a reference to MS Excel 14.0 Object Library in the Visual Basic Editor.

Then in the immediate window write ?xlToRight. Thats quite enough.

edited Feb 27, 2017 at 16:16

answered Feb 27, 2017 at 15:22

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Oxy111 Over a year ago

Thank you for the response, So I just went into task manager, and indeed there was a lot of excel processes running , so I closed those. But I still get the same error the only thing I changed from your code was the path. Edit: If It helps my_xl_workbook.Sheets(x).Range("A1", my_xl_workbook.Sheets(x).Range("A1").End(xlToRight)).WrapText = True is what is being highlighted when I press debug

Vityata Over a year ago

Thats strange. Try again with the new code with debug.print and see what is it printing in the immediate window.

Oxy111 Over a year ago

So I copied your new code, and it's stopping at .Range("A1", .Range("A1").End(xlToRight)).WrapText = True with nothing in the Immediate window

Oxy111 Over a year ago

Yes, everything is closed and it is the correct path, I renamed my file to 123.xlsx and chose an easier path to test and still same results. "C:\Users\Desktop\123.xlsx"

Oxy111 Over a year ago

Thank you so much that worked! But why is it -4161? what is late binding?

|

Collectives™ on Stack Overflow

MS Access VBA format Excel

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related