Refer to Excel worksheet from Access VBA 1

[oticonaus]

I have a form which populates values from an Access table into an Excel worksheet through VBA.

Set ExcelSheet = GetObject("Test Sheet Totals 20042.xls")
ExcelSheet.Application.cells(row, column) = tmp

This worked fine until this year, as I need to specify a different worksheet to populate. How do I do that?

 

[lewds]

Hi..

Is it possible to see more of your code, as to the actual transfer process?

To look at the possible places to transfer to, look in the Range Naming function in Excel.

 

[oticonaus]

Sure.

    Dim c As Integer, Week As Integer, row As Integer, column As Integer, deptcount As Integer
    Dim dep As String, tmp As String, ssql As String
    Dim ExcelSheet As Object
    Dim db As Database
    Dim rst As DAO.Recordset

    Set db = CurrentDb
    
    ssql = "SELECT Departments.Department FROM Departments"
    Set rst = db.OpenRecordset(ssql)
    rst.MoveLast
    deptcount = rst.RecordCount

    Set ExcelSheet = GetObject("\\Sydnt1\Common\Administration\Test Sheet Totals 20042.xls")
    'Set ExcelSheet = GetObject("c:\Test Sheet Totals 20042.xls")
    Week = Forms!ExportData.txtWeek
    column = Week + 59
    row = 3
    c = 1
        
    ' Export the top section of the spreadsheet
       
    DoCmd.Hourglass True
    
    For c = 1 To deptcount
        dep = DLookup("[Name]", "Departments", "[ESortOrder]=" & c)
        
        tmp = Nz(DLookup("[Govnew]", "TestData", "[Week]=" & Week & "AND [Dept]='" & dep & "'"), 0)
        ExcelSheet.Application.worksheets("sheet 3 2005").cells(row, column) = tmp
        tmp = ""
        tmp = Nz(DLookup("[Govold]", "TestData", "[Week]=" & Week & "AND [Dept]='" & dep & "'"), 0)
        ExcelSheet.Application.cells(row + 1, column) = tmp
        tmp = ""
                
        row = row + 12      'move to the beginning of the next dept
    Next
        
    DoCmd.Hourglass False
    MsgBox "Upper section exported", vbOKOnly

 

[PHV]

I need to specify a different worksheet
Something like this ?
ExcelSheet.Application.worksheets("another sheet name").cells(row, column) = tmp
...
ExcelSheet.Application.worksheets("another sheet name").cells(row + 1, column) = tmp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[oticonaus]

Sorry! I was very slack in proofing my code before submitting. That is what i tried initially (makes sense!) so that is why my code above reads

tmp = Nz(DLookup("[Govnew]", "TestData", "[Week]=" & Week & "AND [Dept]='" & dep & "'"), 0)
[b]ExcelSheet.Application.worksheets("sheet 3 2005").cells(row, column) = tmp[/b]

for the first section. the original code which I am trying to fix read

tmp = Nz(DLookup("[Govold]", "TestData", "[Week]=" & Week & "AND [Dept]='" & dep & "'"), 0)
[b]ExcelSheet.Application.cells(row + 1, column) = tmp[/b]

When I execute this, it gives me an error

Run time error '9':
Subscript out of range.

and the code has stopped at the first ExcelSheet.Application...(the one I changed).

Hope all that makes sense.

 

[PHV]

Does the "sheet 3 2005" sheet exists in the "Test Sheet Totals 20042.xls" workbook ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[oticonaus]

Hi PHV,

It is fixed!!!

Yes, "sheet 3 2005" existed, but then I changed the worksheet name to just "2005" and it worked a treat. I assume it maybe doesn't like having spaces in the name?

Anyway, thanks for your help.

 

[dc20]

it worked a treat"

Is that good ?

 

[oticonaus]

Sorry, my Aussie slang probably doesn't help. Yes, once I changed the name of the worksheet, the suggested code worked. It seems that the problem was my original worksheet name had spaces.

ExcelSheet.Application.worksheets("another sheet name").cells(row, column) = tmp

Hope that is clear now.