UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Enumerating Worksheets    
 
   
azizrasul
post Nov 28 2007, 10:47 AM
Post#1



Posts: 1,463
Joined: 18-July 00
From: Faisalabad, Pakistan


I have a front end MS Access db. There are 4 data entry clerks who open this db using a shortcut.
originally had the following working code, with a reference to the Excel object library: -
CODE
Public Sub EditWorksheetName(strExcelFile As String, CurrentWorksheetName As String, NewWorksheetName As String)
    Dim objExcelApp As Excel.Application
    Dim ws As Excel.Worksheet
    
    Set objExcelApp = New Excel.Application
    
    With objExcelApp
        .Workbooks.Open FileName:=strExcelFile
        .Visible = False
    
        For Each ws In .Worksheets
            If ws.Name = CurrentWorksheetName Then
                ws.Name = NewWorksheetName
            End If
        Next ws
        
        .DisplayAlerts = False
        .ActiveWorkbook.SaveAs strExcelFile
        .Quit
        .DisplayAlerts = True
    End With
    
    Set objExcelApp = Nothing
End Sub

HAs each of the data entry clerks has a different filepath to the Excel object library reference, I have removed the reference on the db and I'm trying to use different VBA code using OBJECTS to do the same as the above code.
What would be the new equivalent code using OBJECTS, which would also let me enumarate thw worksheets? I know it starts of something like: -
CODE
Dim objExcelApp As Object
Set objExcelApp = CreateObject("Excel.Application")
objExcelApp.Workbooks.Open strExcelFile
Go to the top of the page
 
Doug Steele
post Nov 28 2007, 10:59 AM
Post#2


UtterAccess VIP
Posts: 22,192
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Dim ws As Object
Go to the top of the page
 
azizrasul
post Nov 28 2007, 11:20 AM
Post#3



Posts: 1,463
Joined: 18-July 00
From: Faisalabad, Pakistan


OK, I tried it on the original code and it worked. But if I try the same thing on the following code, I get an error 'Object required' within the If statement:-
CODE
Public Sub SortColumn(strExcelFile As String, strWorksheet As String)
    Dim objExcelApp As Object
    Dim ws As Object
    Set objExcelApp = CreateObject("Excel.Application")
    objExcelApp.Workbooks.Open strExcelFile
    objExcelApp.Visible = True
    
    With objExcelApp
        .Workbooks.Open FileName:=strExcelFile
        For Each ws In .Worksheets
            If ws.Name = "tblMissingDates" Then
                .Range("A1:E" & .ActiveSheet.UsedRange.Rows.Count).Sort Key1:=ActiveSheet.Range("A2"), Order1:=xlAscending, Key2:=ActiveSheet.Range( _
                "B2"), Order2:=xlAscending, Key3:=ActiveSheet.Range("D2"), Order3:=xlAscending, _
                Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
                xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:=xlSortNormal
                .Range("A1").Select
            End If
        Next ws
        .DisplayAlerts = False
        .ActiveWorkbook.SaveAs strExcelFile
        .Quit
        .DisplayAlerts = True
    End With
    
    Set objExcelApp = Nothing
End Sub

Am I referencing incorrectly by using CreateObject.
Go to the top of the page
 
azizrasul
post Nov 29 2007, 11:44 AM
Post#4



Posts: 1,463
Joined: 18-July 00
From: Faisalabad, Pakistan


I got it working. Here's the code.
ublic Sub SortColumn(strExcelFile As String, strWorksheet As String)
Dim objExcelApp As Object
Dim objWorkBook As Object
Dim objWorkSheet As Object
Set objExcelApp = CreateObject("Excel.Application")
Set objWorkBook = objExcelApp.Workbooks.Open(strExcelFile)
objExcelApp.Visible = True

For Each objWorkSheet In objWorkBook.Worksheets
If objWorkSheet.Name = strWorksheet Then
objWorkSheet.Range("A2:E" & objWorkSheet.UsedRange.Rows.Count).Sort _
Key1:=objWorkSheet.Range("A2"), _
Key2:=objWorkSheet.Range("B2"), _
Key3:=objWorkSheet.Range("D2"), _
Order1:=1, Order2:=1, Order3:=1 'For descending, use 2.
objWorkSheet.Range("A1").Select
End If
Next objWorkSheet

With objExcelApp
.DisplayAlerts = False
.ActiveWorkbook.SaveAs strExcelFile
.Quit
.DisplayAlerts = True
End With

Set objWorkBook = Nothing
Set objExcelApp = Nothing
End Sub
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    23rd September 2019 - 02:03 AM