Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    2000 upgrade w/ module (97 -2000)

    Hello I have a DB created in office 97 and we would like all of the users to be able to upgrade to access 2000 soon. somoe of the users have upgraded to 2000 but a problem exists with the modules created in the 97 version. i know i need to edit it to make it readable for access 2000 but i do not know how or where toe veen start. i only have 3 modules and all are pretty basic. if you guys could take my code and "format" it i am sure i could fix the others. thanks a lot. also a description of what changed would help because then i could use it as a reference. thanks a mil. this is exactly how the code appears. is there anything else i need to do to make my module in 97 runnable in 2000?

    Public Function blnBlnkTran() As Boolean

    Dim appExcel As Excel.Application

    ' Link to Excel using automation
    Set appExcel = CreateObject("Excel.Application")

    ' So you can watch what's happening...
    appExcel.Visible = False


    ' Open Management Action Plan
    appExcel.Workbooks.Open "GatabaseManagement Action Plan.xls"


    ' Runs a macro found in Management Action Plan.xls, called TransferBlank.
    appExcel.Run "Module5.TransferBlank"
    ' This macro allows Access to import the issues from the MAP (entered by the user) in Excel into the Action Plan
    ' tables in Access. To understand/troubleshoot this macro open 'G:MetricsManagement Action Plan.xls', hit the
    ' 'enable macros' button that pops up upon opening, and then select 'Tools' -> 'Macro' -> 'Macros', highlight
    ' 'TransferBlank' and select 'Edit'. This will display the macro code that will execute.
    ' Comments will explain the function of the code.

    ' Close the MAP workbook (without saving changes)
    appExcel.Workbooks("Management Action Plan.xls").Close True

    ' Done with Excel (omit ''''' if you want to close Excel)
    appExcel.Quit

    End Function



    Ed

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

    Re: 2000 upgrade w/ module (97 -2000)

    I doubt whether "we guys" can say much about this. Your code calls a macro in Excel. We don't have the source code of this macro. You haven't told us if you get a compile error or a runtime error, in Access or in Excel.

    Try to be more specific and tell us which line of the code is highlighted when the problem occurs. You might try single-stepping through the code.

    In the meantime, two comments:
    <hr>' So you can watch what's happening...
    appExcel.Visible = False<hr>
    Why set Visible = False if you want the user to watch what's happening?
    <hr>' Close the MAP workbook (without saving changes)
    appExcel.Workbooks("Management Action Plan.xls").Close True<hr>
    True means that the SaveChanges argument of the Close method is set to True - this contradicts your comment.

  3. #3
    New Lounger
    Join Date
    Jun 2002
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2000 upgrade w/ module (97 -2000)

    sorry for the lack of info, wil try a lot harder to give as many details as i can type. here goes.

    we have a database that is used with excel to enter in reports for our departmental needs. there was a lot of integration between excel and access when it was created. however, it was created in off 97. we have upgraded a few of the machines and have found out the lack of conjunction with 2000. the project has transfered over pretty well except for a few problms.

    1) i use a switchboard that allows the user to press a button and an import from excel occurs. it looks like this. it runs 2 macros within access that updates some other info and then i have a run code line which uns the following module: (keep in mind it was created in 97)

    Public Function blnDumMAP() As Boolean

    Dim appExcel As Excel.Application

    ' Link to Excel using automation
    Set appExcel = CreateObject("Excel.Application")

    ' So you don't watch what's happening...
    appExcel.Visible = False

    ' Open Dummy File, which contains all unique names found in all of the various "responsible" fields
    ' (e.g. Primary Responsible, Primary Responsible2, Secondary Responsible, Secondary Responsible3)
    appExcel.Workbooks.Open "catabaseDummy.xls"
    ' Open Management Action Plan excel file
    appExcel.Workbooks.Open "catabaseManagement Action Plan.xls"

    appExcel.Visible = True

    'Open hidden excel file MAPHolder, which contains a macro that will copy the "responsible" data from the dummy
    'file to the Management Action Plan
    appExcel.Workbooks.Open "catabaseMAPHolder.xls"
    ' To troublshoot, if necessary, open 'c:MetricsMAPHolder.xls', hit the 'enable macros' button
    ' that pops up upon opening, and then select 'Tools' -> 'Macro' -> 'Macros', highlight 'MapHold' and select 'Edit'.
    ' This will display the macro code that will execute. Comments will explain the function of the code.

    'Run macro that copies data from Dummy file to MAP
    appExcel.Run "Module1.MapHold"

    'Close the dummy and MAPHolder files
    appExcel.Workbooks("Dummy.xls").Close False
    appExcel.Workbooks("MAPHolder.xls").Close False

    'Delete the dummy file (so that Access does not ask the user whether he/she wants to overwrite it each time
    Kill "catabaseDummy.xls"
    End Function

    As you can see it opens an excel file that then runs this macro:


    Sub MapHold()
    '
    ' MapHold Macro
    ' Macro recorded 1/10/02 by Edward Santevecchi
    '

    Application.ScreenUpdating = False


    ' COPIES OVER RESPONSIBLE COLUMN
    ' INSERTS N/A, TBD INTO COLUMN AND REHIDES
    Windows("Dummy.xls").Activate
    ' Makes Dummy.xls the active window
    Range("A2:A3").Select
    ' Selects cells A2 and A3
    Selection.Insert Shift:=xlDown
    ' Inserts two blank cells in A2 and A3 and shifts existing cells down
    Range("A2").Select
    ' Makes A2 the active (selected) cell
    ActiveCell.FormulaR1C1 = "N/A"
    ' Inserts the text "N/A" in cell A2
    Range("A3").Select
    ' Makes A3 the active (selected) cell
    ActiveCell.FormulaR1C1 = "TBD"
    ' Inserts the text "TBD" in cell A3
    Columns("A:A").Select
    ' Selects (highlights) column A
    Selection.Copy
    ' Copies selected column (column A)
    Windows("Management Action Plan.xls").Activate
    ' Makes Management Action Plan.xls the active window



    Columns("AG:AG").Select
    ' Selects column AG
    ActiveSheet.Paste
    ' Pastes data copied from the dummy file to column AG in the MAP
    Columns("AG:AG").Select
    ' Selects column AG
    Selection.EntireColumn.Hidden = True
    ' Hides column AG
    Range("AJ1").Select
    Windows("Dummy.xls").Activate
    Application.CutCopyMode = False


    Application.ScreenUpdating = True
    Windows("Management Action Plan.xls").Activate
    Range("A2").Select




    End Sub

    I dont know where the problem is but there is no importing occuring. when i check my table the data isnt there. i dont get any errors just no data???? so i figure the coding is compatible and the comp cant read the new stuff. how do i fix my coding to become accepable for 2000.

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

    Re: 2000 upgrade w/ module (97 -2000)

    I'm sure it must be because I'm dense, but I don't see any import happening.

    Your Access code as posted, as far as I can see, does the following:
    1. <LI>It starts Excel.
      <LI>In Excel, it opens three workbooks Dummy.xls, Management Action Plan.xls and MAPHolder.xls.
      <LI>It runs an Excel macro (from MAPHolder.xls) that copies a column from Dummy.xls to Management Action Plan.xls.
      <LI>It closes Dummy.xls and MAPHolder.xls.
      <LI>It deletes Dummy.xls.
    Probably, there is more to this that you didn't post:
    <UL><LI>Management Action Plan.xls should be saved and closed.
    <LI>The instance of Excel you started should be closed (quit).
    <LI>Something should be imported into Access .[/list]Regards,
    Hans

  5. #5
    New Lounger
    Join Date
    Jun 2002
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2000 upgrade w/ module (97 -2000)

    thanks for the reply. in the macro for the access button that runs the above coding i then i have an action transferspreadsheet. this is where access takes the data transfered on the management action plan.xls file and then imports the formatted data into access. actually it takes a sheet in excel because the original entering of the data and the macro formatting occur within the same spreadsheet.

    for the transferspreadsheet action i have the following:

    transfer type: import
    spreadsheet type: Microsoft excel 8-9
    table name: Action Plan 2
    file name: c:databasemanagement action plan.xls
    has field names: yes
    range: Blank!A:W


    this use to work back in the 97 version, if my coding is ok for the 2000 format then is it something to do with my importing action? i can not thank you enough for this help. so many weeks were spent creating this and its looking like it might be junked now. thanks a lot for eveything.

Posting Permissions

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