[AccessD] Preventing user prompts from Excel

Since you are already in the spreadsheet when you rename it why not read the
data into a table right then and there. No linking. In fact, you dont even
need to rename the sheet because you can use appexcel.worksheet(1) syntax.
Something like  the code that follows. BTW I solved the problem from an
earlier thread of how to force text "numbers" to be recognized by Excel as
numbers if that is still a problem for you.
Regards,
Jim Hale

.WorkSheets(1).Select

'erase old data

strQry(1) = "DELETE tblOutPut_Data.*, tblOutPut_Data.DEPT,
tblOutPut_Data.COMPY " _
& " FROM tblOutPut_Data WHERE (((tblOutPut_Data.DEPT)=" & intBusUnit & ")
AND ((tblOutPut_Data.COMPY)=" & intCo & "));"

DoCmd.RunSQL strQry(1)

'Read results into tblOutPut_Data table
Set dbs = WhichDB("tblOutPut_Data")  'CurrentDb()

'opens the Plan table
Set rstbase = dbs.OpenRecordset("tblOutPut_Data", dbOpenTable)
On Error Resume Next
.Range("A2").Select 'select first cell of first record to read
Dim x As Integer

Do While Not IsEmpty(ActiveCell)
    rstbase.AddNew 'create records in output table
    For x = 0 To 16
    If x < 4 Then rstbase.Fields(x) = .ActiveCell.Offset(0, x)
        'change sign on Jan-Dec revenue
        If x > 3 Then
        If rstbase.Fields("rptline") = 4100 Then
            rstbase.Fields(x) = -Round(.ActiveCell.Offset(0, x), 3)
        ElseIf rstbase.Fields("rptline") = 4110 Then
            rstbase.Fields(x) = -Round(.ActiveCell.Offset(0, x), 3)
        ElseIf rstbase.Fields("rptline") = 4120 Then
            rstbase.Fields(x) = -Round(.ActiveCell.Offset(0, x), 3)
        ElseIf rstbase.Fields("rptline") = 4130 Then
            rstbase.Fields(x) = -Round(.ActiveCell.Offset(0, x), 3)
        ElseIf rstbase.Fields("rptline") = 15000 Then
           rstbase.Fields(x) = Round(.ActiveCell.Offset(0, x), 3)
        Else
             rstbase.Fields(x) = Round(.ActiveCell.Offset(0, x), 3)
        End If
    End If
        
    Next x
    If Not rstbase.Fields("rptline") = 0 Then rstbase.Update
    
'This error occurs if duplicate Business Unit are loaded into the table.
'Business Unit is a primary key so duplicates are not allowed
        If Err.Number = 3022 Then
            MsgBox "Error #: " & Err.Number & " This record is already in
the Database!", 16, "Error"
            Err.Clear   ' Clear Err object fields
        End If
            
    .ActiveCell.Offset(1, 0).Select 'find first cell of next record
Loop


-----Original Message-----
From: jwcolby [mailto:jwcolby at colbyconsulting.com]
Sent: Tuesday, July 13, 2004 9:57 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Preventing user prompts from Excel


That's what I like, a man with multiple valid suggestions.

The answers in all cases is simply that I have always just opened the damned
spreadsheet and renamed the sheet to what I wanted.  I guess I could open
the sheet, close it, open the link and edit that.

I am getting a spreadsheet from a client, attached to an email.  I use
outlook (withevents) to copy the email to a specific email folder (using a
rule) then raise an event for each attachment.  My code in another class
figures out what to do with the attachment including just using the messages
.save method to put it in a folder on the disk.  I then take that saved
email and copy it to another location with a common name (always the same),
open the spreadsheet (if it is a spreadsheet, some aren't) and rename the
interior sheet since clients can NEVER be expected to name the sheets the
same thing from day to day).  Having done all that I have a spreadsheet of a
given name with an interior sheet of a given name and the link just works.  

The alternative is to (and I have done this also) save the sheet, get the
interior sheet name, open the table def, and edit the filename and sheet
name portions and save the tabledef back again.

Both do work, both require Excel automation to open the sheet.

Anyway, thanks for the suggestions.

John W. Colby 
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of StaRKeY
Sent: Tuesday, July 13, 2004 4:16 PM
To: 'Access Developers discussion and problem solving'
S