Manipulating a range of cells in Excel from Microsoft Access

Hi

I am using Office 365  -  64 bit

I have a report in Microsoft Access that I wish to export to an excel sheet.   This works fine.
Immediately after creating the excel workbook, I wish to modify the some things.

I am using the following code to open the workbook.
Public Function DeleteRowInExcel(ByVal MyFileName As String)
On Error GoTo Err_DeleteRowInExcel

    Dim xlApp As Excel.Application
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet
   
    Set xlApp = CreateObject("Excel.Application")
    Set wb = xlApp.Workbooks.Open(MyFileName)
    Set ws = wb.Sheets(1)

Open in new window



I would like to be able to select a range  cells(4,1) to the last row / column containing data     and manipulate as  required
 By  example, set all the cells in the range = 4.  I am having trouble with selecting the range from Microsoft Access VBA.


Thank you
peispudAsked:
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.

chaauCommented:
You need to use Range:
    Dim xlApp As Excel.Application
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet
    Dim Range As Excel.Range
   
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    Set wb = xlApp.Workbooks.Open("c:\temp\test.xlsx", Default, False)
    Set ws = wb.Sheets(1)
    Set Range = ws.Range(ws.Cells(1, 1), ws.Cells(4, 4))
    Range.ClearContents
    wb.Save
    wb.Close

Open in new window

Range takes two parameters of Cell type. The first one is the start Cell, and the second one is the end Cell. The example above Clears the content of the Range (A1:C4)
0
peispudAuthor Commented:
Thank you for your reply but that is not quite what I am looking for.

I know the beginning of the range.  The beginning of the range will be cell 4,1
I need the vba code to start from cells(4,1) to the last row and column of data in the worksheet.

Thank you.
0
chaauCommented:
Then use the Columns collection.
Set Range = ws.Columns(3)

The Range variable in this case will hold the content of the whole Column "C"
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

jadedataMS Access Systems CreatorCommented:
To find the last colof data in a spreadsheet:

Dim lxCol
    With ActiveSheet
        lxCol= .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With
    MsgBox lxCol & " is the last column of this Sheet."

or last row

Dim lxRow As Long
    With ActiveSheet
        lxRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    MsgBox lxRow & " is the last row of this column."
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
peispudAuthor Commented:
Thank you for your help.
0
jadedataMS Access Systems CreatorCommented:
you're ever so welcome!
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.