Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel and Access, missing lines (Office 2k)

    Hello Folks

    OK, I have a problem with an Access 2000 database, it's linked to Excel tables, created by a third party software package, that are regularly replaced. These tables / spreadsheets, should hold around 10k lines, but Access shows most the them as empty records. I've found a few references to the problem, here and on the MSKB, and a work around involving opening and saving the Excel sheets as the most recent version (this solves the missing records problem, thanks folks). My problem now is that the user doesn't always remember to open the Excel sheets and save them as the latest version...... Is there any way I can make Access check the version of Excel and run the save automatically if the version isn't the 2k?? I seem to remember seeing something like this but can't find any reference now. Even if all I can do is check the Excel version and open a message box saying "oy you, up date Excel", or some thing more polite maybe??? :-) :-) then that would stop the regular "my data is missing" phone calls I'm getting now.

    Thanks for the help

    Ian

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel and Access, missing lines (Office 2k)

    This is how you would Check the Access Version in Code:
    SysCmd(acSysCmdAccessVer) = 8 'Access97
    SysCmd(acSysCmdAccessVer) = 9 'Access2000
    SysCmd(acSysCmdAccessVer) = 10 'Acess2002

    If theres a way to do the same in Excel? I don't know [img]/forums/images/smilies/sad.gif[/img] <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel and Access, missing lines (Office 2k)

    In Excel, Application.Version returns the version number of the Excel applicaton, but that doesn't help determining for which version of Excel the workbook was made; I think that is what 'dazednconfused' wants to know. Unfortunately, I have no idea how to get at that information.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel and Access, missing lines (Office 2k)

    I see, no go then <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

    Does it matter what version the spreasheet is? Would'nt a "save as" work on any version?

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Excel and Access, missing lines (Office 2k)

    To determine which file format/version a workbook was saved in, you can use the Workbook FileFormat property. Example:

    ?ActiveWorkbook.FileFormat
    43
    ' Const xlExcel9795 = 43 (&H2B)

    In this example workbook was saved in "Microsoft Excel 97-2002 & 5.0/95 Workbook" format. Note that, as illustrated above, the property returns a numerical value; you will have to browse a long list of constants as defined by Enum XlFileFormat in Object Browser to "translate" numerical value to something more meaningful. You would use the Workbook SaveAs method to specify which format to save workbook as, using one of the XlFileFormat constants. Ex:

    ActiveWorkbook.SaveAs "MyWorkbook", xlWorkbookNormal

    Saves active workbook in current version of Excel. As suggested, to be safe, why not just save the workbooks in whatever the current version is?

    HTH

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel and Access, missing lines (Office 2k)

    Hi Mark,

    Thanks, didn't know that one. I agree with you and Rupert that it's easier and safer to save *all* workbooks.

  7. #7
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel and Access, missing lines (Office 2k)

    Mark

    Looks like this may be my solution.

    I'll get the code to check the attached worksheets every time the database is opened.

    I'm trying to avoid the save as scenario as the system may be used by multiple operators once I've achieved a satisfactory result with the current guinea pig, oops, operator. As there will be multiple users I don't want them all having to open the excel sheets and click save, I'll be getting phone calls telling me the sheets wont open from now to the day I'm carried away in a white van.....

    I'm thinking that all I have to do is set the code to check that the value returned by the code is equal to whatever version I'm running, if not then tell excel to save as the current version?

    Thanks for the help, and I'm amazed that some one knew some thing that Hans didn't, gives me hope for the future yet <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Only kidding Hans <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Thanks again folks, Bill should sponsor this place, beat the MSKB hands down.

    Ian

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel and Access, missing lines (Office 2k)

    You can use Automation to do this. In the Visual Basic Editor, select Tools/References... and set a reference to the Microsoft Excel 9.0 Object Library (9.0 is the version number for Office 2000; if you migrate to Office XP, use 10.0).

    The following code will start an invisible instance of Excel, open the specified workbook, test the version, and save it under the same name but in the current version if necessary. You must provide the path and filename of a file as argument, for example TestWorkbook "P:ImportMonthlyData.xls".

    <img src=/w3timages/blueline.gif width=33% height=2>

    Sub TestWorkbook(strWorkbook As String)
    Dim xlApp As New Excel.Application
    Dim xlWkb As Excel.Workbook

    On Error GoTo ErrHandler

    ' No interaction with user
    xlApp.DisplayAlerts = False
    ' Open workbook
    Set xlWkb = xlApp.Workbooks.Open(FileName:=strWorkbook)
    ' Test version
    If xlWkb.FileFormat <> xlWorkbookNormal Then
    ' Save as current version
    xlWkb.SaveAs FileName:=strWorkbook, FileFormat:=xlWorkbookNormal
    End If

    ExitHandler:
    ' Cleaning up
    If Not xlWkb Is Nothing Then
    xlWkb.Close SaveChanges:=False
    Set xlWkb = Nothing
    End If
    If Not xlApp Is Nothing Then
    xlApp.Quit
    Set xlApp = Nothing
    End If
    xlApp.DisplayAlerts = True
    Exit Sub

    ErrHandler:
    ' Inform user
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    <img src=/w3timages/blueline.gif width=33% height=2>

  9. #9
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel and Access, missing lines (Office 2k)

    Hans

    Thanks for this information, I'd just tried some thing along similar lines, but it's giving me problems. Excel can't open a file that is being used by an Access database, even if it's linked and not yet in use. I'm now considering one of two options, an intermediate database, or leaving the country....... The intermediate database would just contain a form and the code to open, check, save and close the Excel files then open the 'working database' and close itself.

    I'll try your suggestion before complicating things to that extent, it may be my methods are flawed (just for a change <img src=/S/smile.gif border=0 alt=smile width=15 height=15>).

    Thanks

    Ian

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel and Access, missing lines (Office 2k)

    Hi Ian,

    I don't think that you need an intermediate database. If the workbook is open in Access, Excel can't open it, but just having a link in Access to an Excel workbook doesn't mean that the workbook is in use.

    I just did a test, and successfully opened and saved an Excel workbook that was also linked in Access (but not open).

    Make sure that all queries, forms and reports that refer to the linked table are closed, as well as recordsets based on the linked table, before you run the code to check the version.

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Excel and Access, missing lines (Office 2k)

    You could go another way totally, and that is to keep the excel stuff in Access (it's a multi user database after all) and anything a users wants in excel they can just export it from access.

    Just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> worth.
    Pat

  12. #12
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel and Access, missing lines (Office 2k)

    Patt

    Nice idea, but the data is input through an MRP system called BPCS, the user friendliness of which is non existent. The reason I'm using Access + Excel is that our version of BPCS will only export up to Excel 4 with any header information.

    Replacing or upgrading the MRP isn't on the cards, so we're setting up Access systems and producing Excel files overnight. This is saving our users several hours a week, days in some cases <img src=/S/smile.gif border=0 alt=smile width=15 height=15>.

    Thanks for the suggestion.

    Ian

  13. #13
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Excel and Access, missing lines (Office 2k)

    <hr>and I'm amazed that some one knew some thing that Hans didn't<hr>
    Actually I was also very surprised that Hans overlooked this, we'll just have to revoke his status as Official Guru.... <img src=/S/clever.gif border=0 alt=clever width=15 height=15> (JOKE)

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel and Access, missing lines (Office 2k)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> I'll hide in shame <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    In fact, you'd be amazed at how much I don't know - something brought home to me every day when I browse the forums.

    Hans

Posting Permissions

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