Results 1 to 14 of 14
  1. #1
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664

    What is wrong with is Excel program?

    I know the title of this forum is AccessForums.net, but I do have a small question on Excel ;I am trying to prepare an Excel files for importation into MS Access. The first thing that I must do


    is delete 4 row that contains only columns titles -n data.

    Excel can only recognize that one row contains the columns' title. So rows 1 to 4 must go and 5 stays.

    I have written a little VBA program to do just that. I have plan to add to it once this section complete and running.

    When I click on it nothing happens.

    Now I added the line Msgbox "Hello" right above the delete line and when I press the button it does open a window saying hello.

    So of know the box is working, but the line to delete the 4 rows in the Excel spreadsheet is not working.

    What is wrong and how do I fix it?

    Any help appreciated. Thanks in advance.

    Respectfully,

    Lou Reed
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    I looked at your code and the sheet. When I click on the button, the code runs and deletes rows 1:4. Cannot replicate your issue.

  3. #3
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    Wait a minute that did not happened to me! It was an *.xlsm file so that should not have been a problem.

    I will try again.

    Thanks for your input.

    Respectfully,

    Lou Reed

  4. #4
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    You are right. I was to quick to judge and observe or whatever. First, I must allow edit so I have to get rid of the yellow band at the top and second the action seems to only happen when I release the button
    not when I initially press the button.

    It does work however and that is what is important.

    Respectfully,

    Lou Reed

  5. #5
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664

    I cannot change the nameof a column

    Okay, here is another one. I am trying to change the title of Column A from # to ID. It should work, but it does not. The first line does work and columns 1-4 rows deleted, but the title for
    column A is still # not ID.

    The Excel file is attached.

    What is wrong? Any help appreciated. Thanks in advance.

    Respectfully,

    Lou Reed
    Attached Files Attached Files

  6. #6
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    I am trying to change the title of Column A from # to ID
    Actually, no - that is not what you are doing - you are changing the content of a cell from "#" to "ID". Columns don't have titles - it is just a row (usually row 1) that contains values that act as, or look like, column titles.

    First, your syntax to reference a single cell is not correct. It should look like this:

    Range("A6") = "ID" ' You are not changing the name of the range, you are changing the value in the cell, so .name was incorrect.

    Second, make all the heading (cell content) changes BEFORE you do the deletions, because after you delete the rows, "A6" is no longer "A6" - it has become "A2". It's a lot easier to write cell references when you can see what they are.

    And, one tip - when you write an Excel macro, save the file BEFORE you run the macro. If testing/running the macro messes up the worksheet (and believe me, that is very easy to do), all you need to do is close the file WITHOUT saving; then when you reopen it, the macro is still there and you don't have to waste time rewriting it (painful experience taught me that one).

  7. #7
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    I understand what you say. I think that you meant

    Range("A5") + "ID"

    Not

    Range("A6") = "ID"

    The # is in cell A5 not A6.

    I always save my documents and spreadsheets before any major changes. That way when I run the change and do not like it then i just do not save the changes.

    It works every time.

    Respectfully,

    Lou Reed

  8. #8
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664

    Did I delete the correct row?

    i am confuse abut the result of my last modification of the Excel Spreadsheet by way of VBA commands.

    It is the step where I remove row 4. That comes after I have removed rows- 1-4. It seems to have removed row 3 instead. An
    examination of the code shows that row 4 is the one that was removed (or should have been removed).

    Any help appreciated. Thanks in advance.

    Respectfully,

    Lou Reed
    Attached Files Attached Files

  9. #9
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    The # is in cell A5 not A6.
    Oops! Yes, sorry, my mistake.

    What you need is : Range("A5") = "ID"

    It is the step where I remove row 4. That comes after I have removed rows- 1-4. It seems to have removed row 3 instead.
    Look VERY carefully at the left side of the spreadsheet where the row numbers are. You will see (before you delete anything) that there is an almost, but not quite, hidden row 7. If you expand that row (i.e. make it higher), you will see that there is data in it (only in one cell, but data nonetheless), so I wasn't going to delete it. Is that row required, or is it a mistake?

    If it's a mistake, all you need to do is delete it as well.




  10. #10
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    I will find out from the people who are paying me what exactly that is. I just do not know.

    Thanks for your help.

    Respectfully,

    Lou Reeed

  11. #11
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    Code:
    It is the step where I remove row 4. That comes after I have removed rows- 1-4. It seems to have removed row 3 instead. An e
    Code:
    xamination of the code shows that row 4 is not the one that was removed (or should have been removed).
    When I delete row four now it seems that another row is deleted -not four.I will post a picture when I get one.

    Respectfully,

    Lou Reed

    PS. Okay, here is a copy of the Excel file and a snippet capture of the result when the button is pressed to prepare the file for export. As I said it seems that row three is deleted when i wanted row to be deleted.
    Attached Thumbnails Attached Thumbnails Capture.jpg  
    Attached Files Attached Files

  12. #12
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Row three is not deleted - it is still there, but almost hidden. When you delete a row in Excel, the remaining rows are renumbered; there is never a gap in the numbering. If you see what looks like a gap in the row numbering, you know there is a hidden row between the ones you do see.

    Your macro is working as it should (i.e. as it was written to to). You are going to have to dtermine what to do with the almost-hidden row 3 - the one with only one value in it. Do you need it or not?
    Last edited by John_G; 09-28-2017 at 05:07 PM. Reason: Add a comment

  13. #13
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    I understand what you said, but I do not see row 3. So if it is there how do I see it. I cannot see it now.

    Respectfully,

    Lou Reed

  14. #14
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    It's a small spreadsheet, so do it this way:

    - Select the entire spreadsheet by clicking on the small square above Row 1 and left of column "A". The whole spreadsheet will be highlighted.
    - Right-click anywhere in the highlighted area
    - From the dropdown menu, select Row Height...
    - Enter 25 as the new row height, click OK

    All the rows will now be visible.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Wrong headings in excel file
    By deepucec9 in forum Reports
    Replies: 3
    Last Post: 11-25-2015, 10:45 PM
  2. wrong excel worksheet opening
    By xopherira in forum Programming
    Replies: 8
    Last Post: 08-28-2015, 12:10 PM
  3. Excel to Access/Program
    By anthony.maddick in forum Import/Export Data
    Replies: 1
    Last Post: 04-22-2015, 07:08 AM
  4. Replies: 3
    Last Post: 06-17-2014, 02:58 AM
  5. Linked Excel Table Showing Wrong Value
    By lukejc1 in forum Import/Export Data
    Replies: 1
    Last Post: 11-21-2012, 10:37 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums