Edit Excel workbook from Access 1

[sabavno]

Hi,

I wonder if anyone could show me how to rename the several worksheets within one excel workbook that I access from within the Access module.

What I do is I let user choose a excel file, then create Excel object

Dim xls As Excel.Workbook
Dim wks As Excel.Worksheet

Set xls = Workbooks.Open("C:\File.xls&quot
Set wks = xls.Worksheets

The excel file has many sheets in it. I want to be able access each and rename it. I also want to edit the data type format in every sheet.

Please advise.
Any hints would be greatly appreciated.

Thanks.

 

[Nikita6003]

Hiya,

you could either loop through the worksheets & rename/reformat as you go (the example uses number format), or explicitly call each worksheet:

Loop thu' collection:

    Dim xls As Workbook
    Dim wks As Worksheet
    Dim l_iCounter As Integer
    
    l_iCounter = 1
    Set xls = ThisWorkbook
    For Each wks In xls.Worksheets
        'can be used if you name the sheets using the same text & a counter
        '& that's also the *dis*advantage of using the loop
        'Advantage = quick coding
        wks.Name = "Name" & l_iCounter
        wks.Cells.NumberFormat = "#,##0.00"
        l_iCounter = l_iCounter + 1
    Next wks

Alternatively, set up a subroutine which does this for one sheet & call the routine for each sheet you want to change:

Sub ChangeSheet(wks As Worksheet, wksName As String)
    wks.Name = wksName
    wks.Cells.NumberFormat = "#,##0.00"
End Sub

Sub Test()
    Dim xls As Workbook
    
    Set xls = ThisWorkbook
    Call ChangeSheet(wlx.Sheets(1), "FirstSheet")
    Call ChangeSheet(wlx.Sheets(2), "SecondSheet")
    Call ChangeSheet(wlx.Sheets(3), "ThirdSheet")
End Sub

alternatively, just stick it one sub:

Sub ChangeSheet(wks As Worksheet, wksName As String)
     Dim xls As Workbook
    
    Set xls = ThisWorkbook
    xls.Sheets(1).Name = "FirstSheet"
    xls.Sheets(1).Cells.NumberFormat = "#,##0.00"
    xls.Sheets(2).Name = "SecondSheet"
    xls.Sheets(2).Cells.NumberFormat = "#,##0.00"
    xls.Sheets(3).Name = "ThirdSheet"
    xls.Sheets(3).Cells.NumberFormat = "#,##0.00"
End Sub

methods2 & 3 achieve the same thing - only 2 does it in fewer lines of code
One's useful - it's qiock - but if you want to use different sheet names you'd need to tweak it here & there.

HTH anyway

Cheers
Nikki

 

[sabavno]

Thanks a lot, it's very helpful

I was also wondering if you might know, how to export few Access tables into one excel workbook, making each table be separate sheet in the workbook

Thanks again.