Error in navigating excel range through Access

[aganesan99]

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

ws1.Range("A2:" & ActiveCell.Address(0, 0)).Select

.

Below is the full code. Please help.

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

 

[MarkK]

I am getting error in the step

Hi there. It makes a difference what the error is. Type mismatch? Object or With block variable not set?

 

[JHB]

You need a reference to the Excel object, then ActiveCell is unknown for MS-Access.

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:

    [B][COLOR=Red]ws.Application.[/COLOR][COLOR=Blue]ActiveCell[/COLOR][/B].End(xlToRight).Select

 

[aganesan99]

Thank you JHB. I missed that. Thanks you very much

 

[aganesan99]

Hi there. It makes a difference what the error is. Type mismatch? Object or With block variable not set?

Sorry for not mentioning it MarkK. I was getting Type Mismatch error.

 

[JHB]

Thank you JHB. I missed that. Thanks you very much

You're welcome, good luck.

 

[MarkK]

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,