Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I need to save a file under a new name in a specific place, then save it again as a html file. When I record a macro to that purpose and then execute the macro, it stops for both file saves and asks if I want to replace the file(s) already there. I do in both cases.
    Also, I then want to go back to file 1 and create a range name for the current region. I do not seem to be able to do the range name correctly.

    If anybody can point me in the right direction I would appreciate it.

    Thanks,

    itconc

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi itconc,

    Please post the relevant portions of your code, so that we can see how you're doing the saves.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts
    here it is:
    Sub ex()
    '
    ' ex Macro
    '

    '
    ChDir "C:\Arbeit\CASData"
    ActiveWorkbook.SaveAs Filename:="C:\Arbeit\CASData\NewData.XLS", FileFormat _
    :=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
    False, CreateBackup:=False
    ActiveWorkbook.SaveAs Filename:="C:\Arbeit\CASData\NewData.htm", FileFormat _
    :=xlHtml, ReadOnlyRecommended:=False, CreateBackup:=False
    Columns("D").Select
    Selection.Delete Shift:=xlToLeft
    ActiveWindow.SmallScroll ToRight:=1
    Columns("F:Q").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    ActiveWorkbook.Save

    '
    ActiveWorkbook.Close
    Workbooks.Open Filename:="C:\Arbeit\CASData\NewData.XLS"
    Columns("F:F").Select
    Selection.Delete Shift:=xlToLeft



    Range("A2").Activate

    ActiveWorkbook.Save
    'ActiveWorkbook.Close
    End Sub
    For the rangename I had this:
    Sub Macro3()
    '
    '
    '
    'Range("A1:Q19").Select - I remarked this out.
    '
    ActiveWorkbook.Names.Add Name:="newdata", RefersToR1C1:=selction.CurrentRegion.Select

    Selection.CurrentRegion.Select
    Application.Goto Reference:="newdata"
    End Sub

    Thanks,
    itconc

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    What you need is:

    Application.displayalerts = false
    {your save code}
    Application.displayalerts=true

    For the rangename try:

    Activeworkbook.names.add Name:="your name here", _
    RefersTo:=selection.currentregion.address(,,xlR1C1 )

    Note: the above assumes your cursor is located within the current region! If not you need to locate it there first or do a selection of the current region first then delete the .currentregion from the above code.

    Note 2: Don't miss the two commas in the arguments to the Address property! If you want you can replace that with .address(ReferenceStyle:=xlR1C1)
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Looks great. It works in Excel. I will need to thenimport to Access - that is properly automated and works.
    Thank you so much for your help!
    ITconc

  6. #6
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I am having a problem with the range name. It works the way you suggested. However, during the import, Access does not recognize the range name because it only shows up under insert/name/define not in the regular range name list.
    I am not sure if this now goes under Access.
    Thanks,

    itconc

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Itconc,

    Can you post your Access Import code?
    I tested the create range name code on an excel sheet I have and the name showed up when I tried to use Names,paste.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I see the name under names paste also. However, it does not show up in the name box. If I press Ctrl ain excel and name the name the range through the name box, all is well.
    I am attaching the error code I get in Access, the macro and the vba code.
    Thanks,

    ITconc

  9. #9
    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
    I think you'll find that this line:

    Activeworkbook.names.add Name:="your name here", _
    *RefersTo:=selection.currentregion.address(,,xlR1C 1)


    should be:

    Activeworkbook.names.add Name:="your name here", _
    *RefersToR1C1:=selection.currentregion.address(,,xlR1C1)
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by rory View Post
    I think you'll find that this line:

    Activeworkbook.names.add Name:="your name here", _
    *RefersTo:=selection.currentregion.address(,,xlR1C 1)


    should be:

    Activeworkbook.names.add Name:="your name here", _
    *RefersToR1C1:=selection.currentregion.address(,,xlR1C1)
    Rory, Right your are, it should be! Interestingly it will work the other way at least in 2003 it still generates the same reference for the name!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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