• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 637
  • Last Modified:

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
0
peispud
Asked:
peispud
  • 2
  • 2
  • 2
1 Solution
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
peispudAuthor Commented:
Thank you for your help.
0
 
jadedataMS Access Systems CreatorCommented:
you're ever so welcome!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now