Error in navigating excel range through Access (1 Viewer)

aganesan99

New member
Local time
Today, 10:31
Joined
Sep 2, 2016
Messages
8
Hi All,

I have created the following code that deletes the existing data and copies the data from another excel and refreshes the file before closing.

I am getting error in the step
Code:
ws1.Range("A2:" & ActiveCell.Address(0, 0)).Select
.

Below is the full code. Please help.

Code:
Sub UpdateFinalFiles()
Dim strPath As String, nsheet As String
Dim xlApp As Object, wb As Object, wb1 As Object, ws As Object, ws1 As Object
Dim xlWB1 As Excel.Workbook
Dim objFso As FileSystemObject
Dim objFolder As Folder
Dim objFile As File
Dim mfile As Workbook
Dim rLastCell As Excel.Range, a As Excel.Range


'Application.ScreenUpdating = False
'Application.DisplayAlerts = False
Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = True

'xlApp.ActiveWindow = True

Set wb = xlApp.Workbooks.Open(CurrentProject.Path & "\00_CBCN_Final.xlsb")
strPath = CurrentProject.Path & "\Output Files\"

Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFso.GetFolder(strPath)

For Each objFile In objFolder.Files
 If objFile.Name Like "*.xlsx" Then
    Set wb1 = xlApp.Workbooks.Open(FileName:=objFile, ReadOnly:=False)
    Set ws1 = wb1.Sheets("Data")
    ws1.Activate
    ws1.Range("A2").Select
    If ws1.Range("A2").Value = "" Then
    Else
    ws1.Range("A2:U2").Select
    'ws1.Range(Selection, Selection.End(xlDown)).Select
    ws1.Range("A1").Select
    ws1.Application.ActiveCell.End(xlToRight).Select
    ws1.Application.ActiveCell.End(xlDown).Select
    ws1.Range("A2:" & ActiveCell.Address(0, 0)).Select
    'a = Range(ActiveCell).Address(0, 0)
    'ws1.Range("A2:" & Application.Selection.Address(0, 0)).Select
    ws1.Application.Selection.Clear
    ws1.Range("A2").Select
    End If
    
    Select Case objFile.Name
    Case Is = "Comm and Marketing Costs.xlsx"
        nsheet = "_1_Comm___Marketing_Costs"
    Case Is = "Rent and Lease.xlsx"
        nsheet = "_2_Rent___Lease"
    Case Is = "Other Means of Production.xlsx"
        nsheet = "_3_Other_Means_of_Production"
    Case Is = "Outsourced Services Direct.xlsx"
        nsheet = "_4_Outsourced_Services_Direct"
    Case Is = "Professional services.xlsx"
        nsheet = "_5_Professional_services"
    Case Is = "Repairs and Maintenance.xlsx"
        nsheet = "_6_Repairs___Maintenance"
    Case Is = "Telecom Costs.xlsx"
        nsheet = "_8_Telecom_Costs"
    End Select
    
    wb.Activate
    Set ws = wb.Sheets(nsheet)
    ws.Activate
'    Set rLastCell = ws.Worksheets(nsheet).Application.Cells.Find(What:="*", _
                    After:=ws.Worksheets(nsheet).Cells(1, 1), _
                    LookIn:=xlFormulas, _
                    Lookat:=xlPart, _
                    SearchOrder:=xlByColumns, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False)
       
    'ws.Range("A2:" & rLastCell.Address(0, 0)).Select
    ws.Range("A1").Select
    ws.Application.ActiveCell.End(xlToRight).Select
    ws.Application.ActiveCell.End(xlDown).Select
    
    ws.Range("A2:" & ActiveCell.Address(0, 0)).Select
    
    'Range(Selection, Selection.End(xlDown)).Select
    ws.Application.Selection.Copy
    'Workbooks(objFile.Name).Activate
    ws1.Activate
    ws1.Range("A2").PasteSpecial
    ws1.Application.CutCopyMode = False
    ws1.Application.ActiveWorkbook.RefreshAll
    ws1.Range("A1").Select
    wb1.Sheets("Snapshot").Select
    wb1.Close savechanges:=True
 End If
Next objFile
wb.Activate
wb.Close savechanges:=True
xlApp.Quit
'Application.ScreenUpdating = False
'Application.DisplayAlerts = False

Set xlApp = Nothing
Set wb = Nothing
Set wb1 = Nothing
Set ws = Nothing
Set ws1 = Nothing
Set objFso = Nothing
Set objFolder = Nothing

End Sub
 

JHB

Have been here a while
Local time
Today, 06:01
Joined
Jun 17, 2012
Messages
7,732
You need a reference to the Excel object, then ActiveCell is unknown for MS-Access.
Code:
ws1.Range("A2:" & [B][COLOR=Red]ReferenceGoesHere.[/COLOR][/B]ActiveCell.Address(0, 0)).Select
I see in other codelines you use ws.Application for the reference:
Code:
    [B][COLOR=Red]ws.Application.[/COLOR][COLOR=Blue]ActiveCell[/COLOR][/B].End(xlToRight).Select
 

MarkK

bit cruncher
Local time
Yesterday, 22:01
Joined
Mar 17, 2004
Messages
8,178
Sorry for not mentioning it MarkK. I was getting Type Mismatch error.
Hey, no need to apologize. It's your thread. The better your description of the problem, the more likely someone will be able to help.
Cheers,
 

Users who are viewing this thread

Top Bottom