Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    May 2003
    Location
    Franklin, Tennessee, USA
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import fmAccess, Save as New Worksheet (2000)

    Hi.
    Have a workbook set up to run an import query from Access on open. I would like the user to be able to open, have the data refresh then be able to save as a new worksheet without the query link with a new name of their choosing.
    I am very familiar with Access/Vba, not so much with Excel/Vba.

    Thank you in advance.
    Deb

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Import fmAccess, Save as New Worksheet (2000)

    Something like the following should help :

    Sub UpdateAndCopy()
    Dim qt As QueryTable
    [A1].QueryTable.Refresh
    ActiveSheet.Copy
    For Each qt In ActiveSheet.QueryTables
    qt.Delete
    Next
    ActiveWorkbook.SaveAs Filename:="NewName.xls"
    End Sub

    This assumes that A1 of the activesheet contains is contained in the query range. You can change to suit.

    Andrew C

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

    Re: Import fmAccess, Save as New Worksheet (2000)

    If you want the user to pick the filename, then you can use this modification to Andrew's code:

    <pre>Sub UpdateAndCopy()
    Dim qt As QueryTable
    Dim vFilePath As Variant
    [A1].QueryTable.Refresh
    ActiveSheet.Copy
    For Each qt In ActiveSheet.QueryTables
    qt.Delete
    Next
    vFilePath = GetSaveAsFilename
    If vFilePath <> False Then
    ActiveWorkbook.SaveAs Filename:=vFilePath
    End If
    End Sub
    </pre>

    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
  •