Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    A simple piece of VBA (Excel 2000)

    Not sure if I should have put this in the VBA section really, but it's a simple question.

    As part of a macro I wish to delete a range name BUT at certain stages the name will already have been deleted. So I just want to say 'If MyRangeName exists then delete it else just carry on'

    This is what i've tried:

    If ActiveWorkbook.Names("Import_Marine_FGU").Value <> "" Then
    ActiveWorkbook.Names("Import_Marine_FGU").Delete
    End If

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A simple piece of VBA (Excel 2000)

    Try this code:

    <pre>Dim oName As Name
    On Error Resume Next
    Set oName = ActiveWorkbook.Names("Me")
    On Error GoTo 0
    If Not oName Is Nothing Then
    oName.Delete
    End If
    </pre>

    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A simple piece of VBA (Excel 2000)

    Legare,

    Thanks for the code, it worked a treat, but (there's always a but), after that I import data from access with the 'Data - Get External - Run Saved Query'

    The problem is that the RangeName("Import_Marine_FGU") that was deleted gets recreated as part of this query. Unfortunately it adds an '_1' after it, which I don't want as other files link back to this Range Name.

    This is my poor piece of code, any other suggestions are most welcome!!!

    'Delete Range Name 'Import_Marine_FGU'
    Dim oName As Name
    On Error Resume Next
    Set oName = ActiveWorkbook.Names("Import_Marine_FGU")
    On Error GoTo 0
    If Not oName Is Nothing Then
    oName.Delete
    End If

    ActiveWorkbook.Save

    'Delete last quarters data
    Sheets("Before").Select
    Range("A6").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.ClearContents

    'Import Marine Data
    With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
    "ODBC;DSN=MS Access Database;DBQ=P:INFOTECUK_CSSYS3MAR_ADDMarine Accrual.mdb;DefaultDir=P:INFOTECUK_CSSYS3MAR_ADD;D riverId=2" _
    ), Array("5;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;")), Destination:= _
    Range("A6"))
    .CommandText = Array( _
    "SELECT MarData.LOSS_ID, MarData.LOSSNAME, MarData.CAT_CODE, MarData.PAID_GBP, MarData.PAID_USD, MarData.PAID_CAD, MarData.OSLR_GBP, MarData.OSLR_USD, MarData.OSLR_CAD, MarData.PAID_cSTG, MarData.INC_c" _
    , _
    "STG" & Chr(13) & "" & Chr(10) & "FROM `P:INFOTECUK_CSSYS3MAR_ADDMarine Accrual`.MarData MarData" & Chr(13) & "" & Chr(10) & "ORDER BY MarData.LOSS_ID" _
    )
    .Name = "Import Marine FGU"
    .FieldNames = False
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlOverwriteCells
    .SavePassword = True
    .SaveData = True
    .AdjustColumnWidth = False
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .Refresh BackgroundQuery:=False
    End With

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A simple piece of VBA (Excel 2000)

    I'm afraid that I don't know enough about ODBC to know why that is happening. Hopefully someone else can jump in with an answer.
    Legare Coleman

  5. #5
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A simple piece of VBA (Excel 2000)

    I've discovered that if I delete the range name, close the file, re-open the file & then run the part of the macro that imports the data, I get the range name as desired.

    It's as if it is keeping something in memory - confuuuuuuuused

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A simple piece of VBA (Excel 2000)

    Yes, that is sometimes exctly what you have to do. Excel doesn't clean up some things until you close the file.
    Legare Coleman

Posting Permissions

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