Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    More MODULE TROUBLE (Office 2000)

    I am using the following piece of code to output data from an access database into a spreadsheet:
    DoCmd.OutputTo acOutputQuery, "SFMTradeReport", acFormatXLS, strFileName, True

    I have attached a file with this post. The first 3 lines are the outcome of this code but I want the output to look like the last three lines.

    What am I doing wrong here? Would somebody tell me what I have to do? PLEASE???
    Attached Files Attached Files

  2. #2
    Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More MODULE TROUBLE (Office 2000)

    The OutputTo action is the same as if you chose the "Analyze with Excel" button on the toolbar. This creates an Excel spreadsheet with formatted columns (including formatted column headings). If you do not want the formatted column headings, try using TransferSpreadsheet instead.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: More MODULE TROUBLE (Office 2000)

    Hi,
    TransferSpreadsheet does not have an option to automatically start the relevant application, unlike OutputTo. I imagine your error is because you're trying to activate Excel without actually starting it.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More MODULE TROUBLE (Office 2000)

    So what do I have to do, if I wanna open the file I saved as soon as I save it & set focus to it???

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: More MODULE TROUBLE (Office 2000)

    I'd suggest using the same method you used with your Word document.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More MODULE TROUBLE (Office 2000)

    Hiya! First of all, THANK YOU.
    I've have changed the code to TransferSpreadsheet and everything works except for one thing.
    After tranferring and it should focus on the Excel spreadsheet but it doesn't do that instead it gives me an error on AppActivate "Microsoft Excel"
    The error is: Run time error 5 : Invalid procedure call or argument.

    Would someone please tell me why Im getting this error message and how I could prevent it???
    <pre>Const CSTR_SAVEPATH As String = "S:SRI_WORK_AREADOCUME~1"
    Const CSTR_DOCSPATH As String = "C:TradarDevelopment"

    Sub TestSFM()
    Dim strFileName As String, strMsg As String, vResult As Variant
    Dim strFilenamePart As String
    Dim rstRecipients As DAO.Recordset
    Dim strFund As String
    strFund = "Soros"
    'On Error GoTo ExportSFMReport_Err
    Dim rst As DAO.Recordset, db As DAO.Database
    'Turn System warnings off
    DoCmd.SetWarnings False
    'Delete contents of the table
    DoCmd.RunSQL _
    "DELETE [tblSFMReportSource].* FROM [tblSFMReportSource] WITH OWNERACCESS OPTION;", 0
    'Run Append query to add SFM records to the table
    DoCmd.OpenQuery "AppendToSFMReportSource", acNormal, acEdit
    'Turn System warnings on.
    DoCmd.SetWarnings True
    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblSFMReportSource")
    If rst.BOF And rst.EOF Then
    vResult = MsgBox("There are no records. Would you like to send a fax?", _
    vbQuestion + vbYesNo)
    If vResult = vbYes Then
    'set value to merge
    Set rstRecipients = db.OpenRecordset("Recipients", dbOpenDynaset)
    With rstRecipients
    .MoveFirst
    .FindFirst "[Fund] = '" & strFund & "'"
    .Edit
    !Merge = True
    .Update
    End With
    Set rst = Nothing
    Set db = Nothing
    'Open fax cover
    Set objWord = CreateObject("Word.Basic")
    objWord.AppShow
    'objWord.AppMaximize "", 1 (optional)
    Set objWord = GetObject(CSTR_DOCSPATH & "Fax.doc", "Word.Document")
    objWord.Application.Visible = True
    DoCmd.OpenQuery "UpdRecipients(Merge)", acNormal, acEdit
    Exit Sub
    Else
    Exit Sub
    End If
    Else
    'Export records to spreadsheet and open it
    strFileName = CSTR_SAVEPATH & "BCP" & Format(Now, "DDMMYY") & ".xls"
    vResult = Dir(strFileName)
    If vResult <> "" Then
    vResult = MsgBox("File " & strFileName & _
    " already exists, Would you like to overwrite that file?", vbYesNo)
    If vResult = vbYes Then
    DoCmd.TransferSpreadsheet acExport, 5, _
    "SFMTradeReport", strFileName, False, ""
    MsgBox "Data has been exported successfully.", _
    vbInformation, "Export Confirmation"
    Else
    strFilenamePart = InputBox("File " & strFileName & " already exists," _
    & Chr(10) & "Please enter another filename not including " _
    & Chr(34) & ".xls" & Chr(34) & ": ")
    If strFilenamePart = "" Then Exit Sub
    strFileName = CSTR_SAVEPATH & strFilenamePart & ".xls"
    DoCmd.TransferSpreadsheet acExport, 5, "SFMTradeReport", _
    strFileName, False, ""
    MsgBox "Data has been exported successfully.", _
    vbInformation, "Export Confirmation"
    End If
    Else
    DoCmd.TransferSpreadsheet acExport, 5, "SFMTradeReport", _
    strFileName, False, ""
    MsgBox "Data has been exported successfully.", _
    vbInformation, "Export Confirmation"
    End If
    'Delete contents of the table
    DoCmd.RunSQL _
    "DELETE [tblSFMReportSource].* FROM [tblSFMReportSource] WITH OWNERACCESS OPTION;", 0
    Set rst = Nothing
    Set db = Nothing
    AppActivate "Microsoft Excel"
    End If
    End Sub
    </pre>



    edited to remove scrolling - Rory

  7. #7
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More MODULE TROUBLE (Office 2000)

    Im sorry but what method are you talking about?
    objWord.Activate???
    I tried that and it gives me an error message saying Object required

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: More MODULE TROUBLE (Office 2000)

    Using GetObject to open the spreadsheet in the same way you used it to open your Word document(s).
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More MODULE TROUBLE (Office 2000)

    Rory, I've done as you told me and added the following code to the module:
    Set objExcel = GetObject(strFileName, "Excel.Application")
    objExcel.Application.Visible = True

    but it displays an error message: File name or class name not found during automation operation
    Im sure Im doing something wrong but I don't know what.
    <img src=/S/help.gif border=0 alt=help width=23 height=15>

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: More MODULE TROUBLE (Office 2000)

    Hi,
    Try changing your code to:
    Set objExcel = GetObject(strFileName, "Excel.Workbook")
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More MODULE TROUBLE (Office 2000)

    Thanx Rory Done that but it still is displaying the following error:
    ActiveX component can't create object
    So I thought I'd add the following code to it:
    Set objExcel = CreateObject("Excel.Workbook")
    objExcel.AppShow

    But it doesn't make any difference coz it still gives me the same error

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: More MODULE TROUBLE (Office 2000)

    My apologies, that should have been "Excel.Sheet" not "Excel.Workbook"!
    You might be better off using something like:
    Set objExcel = createobject("excel.application")
    with objexcel
    .visible = true
    .workbooks.open strfilename
    end with

    then you'll need to use

    objExcel.Activate

    to activate the workbook at whatever point you want. Don't forget to use

    Set objExcel = Nothing

    at the end of your procedure.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More MODULE TROUBLE (Office 2000)

    THANX Rory,
    That works fine but there is only one problem. If you have a look in the attached file, you'll see that the date is not formatted as date. I don't understand why coz Its formatted as date in the table but Excel thinks of it as text for some reason
    <img src=/S/help.gif border=0 alt=help width=23 height=15>
    Attached Files Attached Files

  14. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: More MODULE TROUBLE (Office 2000)

    Is there anything in your query that formats it as text? TransferSpreadsheet doesn't do it by default (at least not on my PC)
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #15
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More MODULE TROUBLE (Office 2000)

    What is wrong with the above post???? Have I messed the lounge???

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •