Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jul 2007
    Location
    North Carolina, USA
    Posts
    71
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Macro to Locked Spreadsheet Help (Excel 2003)

    I am pulling data from an access database then I want to use a macro or another method to have it fill in cells in an Excel Document. The catch is that the Excel Document is locked and has merged cells. When I try to export data to Excel and then use a Macro to copy and paste to the Locked Excel Document, it errors out on the cells that are merged. When I try to copy just the data from the cell, it creates a Macro for the specific data (i.e. I copy the date 1/6/2006 from Cell A2. The Macro reads 1/6/2006, so when I change the date in my database, it continues to put 1/6/2006 in my Locked Excel Document.) I am having problems figuring out how to reference the data in a cell so I get the data and not the formatting without specifying the current data. Any help would be appreciated.

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

    Re: Macro to Locked Spreadsheet Help (Excel 2003)

    You have hit on one of the reasons why it is a bad idea to use merged cells in Excel. I avoid them as much as possible.

    Could you tell us in more detail what you're trying to do? Do you want to copy an entire table or query into Excel, or do you want to transfer individual values that are not in a table format?

  3. #3
    Star Lounger
    Join Date
    Jul 2007
    Location
    North Carolina, USA
    Posts
    71
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Macro to Locked Spreadsheet Help (Excel 2003)

    I completely agree with you on the merged cells, however, I am swimming in a sea of office product mis-users. I am building a database to streamline a process for submitting and tracking requests. I cannot control the Spreadsheets (which they have created to be little excel databases with millions of links and formulas) but would still like to limit the end user having to re-type the same information into multiple forms. Because I can not control the forms that are being sent out, I need a way to take the information I am tracking in the new database and paste it into the forms they are using. The spreadsheet I have to put the information in for submitting is locked and recreating it is not an option. Long and short Field A in the database needs to be pasted into a form that has a blank for that information that is a merged cell C5-F5 or similar. Not all of the cells in the spreadsheet are merged.

    I was able to create a giant work around that exports the record information in the appropriate order to Excel and then copy and paste using a macro to the form that is being sent out. I can not recreate the form because it is used enterprise wide and the results from my database are tied to links in the spreadsheet I am sending out.

    With that said I have come up with to errors to the methods I have attempted. Method #1: I copied and pasted while recording a macro, but it only worked for the cells in the spreadsheet form (sending out) that were NOT merged. Method #2: Same export of data, but selected the text using F2 from within the cell and pasted it to the spreadsheet form (sending out), this caused the Macro to record the data not the method of moving it.

    I know there should be a way to edited the macro so it is grabbing the data in a specific cell (location A) and pasting it to a specific cell (location [img]/forums/images/smilies/cool.gif[/img]. And then still having the ability to export over the data and repeat the process, no matter how many times the data in Location A changes.

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

    Re: Macro to Locked Spreadsheet Help (Excel 2003)

    If you want to set the value of cell C5 (which is merged with D5, E5 and F5) on Sheet1 in Book1.xls to the value of cell A1 on Sheet3 in Book2.xls, you can use

    Workbooks("Book1.xls").Worksheets("Sheet1").Range( "C5") = Workbooks("Book2.xls").Worksheets("Sheet2").Range( "A1")

    If both sheets are in the same workbook, you can omit the reference to the workbooks (as long as the workbook is the active one).

    You can also open a recordset in code to retrieve the values of the appropriate record, and set the value of a cell to the value of a field from the recordset. Post back if you want to know more about this approach.

  5. #5
    Star Lounger
    Join Date
    Jul 2007
    Location
    North Carolina, USA
    Posts
    71
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Macro to Locked Spreadsheet Help (Excel 2003)

    This sounds like what I am trying to do, if you can provide more on this, it would be appreciated.
    Although from the example you gave, I am going from a single cell to a cell that may or may not be merged.
    Adding to this, I have to take the information from Access, so if there is a way to cut out the extra excel sheet and possibly use a MACRO in Access to open the Excel Spreadsheet (sending out) and fill using the fields in my Access Record, that would be even better.

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

    Re: Macro to Locked Spreadsheet Help (Excel 2003)

    If you want to run the code from Access, you can use Automation. See the tutorial by our moderator WendellB for some background information:
    Automation 101.

    Start by setting a reference to the Microsoft Excel 11.0 Object Library in Tools | References (in the Visual Basic Editor). This enables you to run Excel invisibly from Access.
    You can now use code like this, substituting the correct names, of course:

    Sub Test()
    Dim xlWbk As Excel.Workbook
    Dim xlWsh As Excel.Worksheet

    On Error GoTo ErrHandler

    Set xlWbk = GetObject("C:ExcelMyWorkbook.xls")
    Set xlWsh = xlWbk.Worksheets("Sheet1")
    xlWsh.Range("C5") =

  7. #7
    Star Lounger
    Join Date
    Jul 2007
    Location
    North Carolina, USA
    Posts
    71
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Macro to Locked Spreadsheet Help (Excel 2003)

    Thank you, I will attempt it and post back if I have problems.

Posting Permissions

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