export file and save it under your local drive

[Zandra]

Hi,
Is there a way to do this:
1) when exporting the file, save the file with the same name as the items in the selected item list box and save all the files under C:\
for instance, if the "Beverage" is exported then i want to save the file as "Beverage.xls". If "Seafood" is exported then i want to save it like "Seafood.xls". Save all files under C:\
the code below works fine except the last line where it exports the file. I only want to change that line of code to reflect what i above explained. thanks. see attached file.

Dim Q As QueryDef, DB As Database
    Dim Criteria As String
    Dim ctl As Control
    Dim Itm As Variant
 
    Set ctl = [Forms]![frmPairedListboxesTables]![lstSelectedItems]
     If ctl.ItemsSelected.Count = 0 Then
          Itm = MsgBox("You must select one or more hospital/s!", 0, "No Selection Made")
        Exit Sub
   End If
    Set DB = CurrentDb()
    For Each Itm In ctl.ItemsSelected
            Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
            Set Q = DB.QueryDefs("Query1")
            Q.SQL = "Select * From tblCategories Where [CategoryName] In(" & Criteria & ");"
          Q.Close
           ' Run the query.
           'DoCmd.OpenQuery "Query1"
           DoCmd.OpenQuery "Query2"
           DoCmd.RunMacro "MyMacro"
    Next Itm

 

[Zandra]

any help will be appreciated.

 

[vbaInet]

Have a look at this method:

Docmd.TransferText

There are options there that would allow you to save into a specific folder.