save as prompt with hard code path

[xopherira]

I would like to be able to close Excel after queries have run and sheet has been written to. A portion of the code I use to open and write is below. Once the cells have been written I would like the file dialog box to open with a hard coded path that also allows user to change. Any ideas? Second code snippet is one way I have been trying, but not able to get it to work.

Dim objXL As Object
Dim objWB As Object
Dim objWS As Object
Set objXL = CreateObject("Excel.Application")
Set objWB = objXL.Workbooks.Open("L:\DS\Data Services\Metrics\Dashboard\Dashboard_v1001.xlsx")
Set objWS = objWB.worksheets("DashBoard")
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim queryName As String


    With objWS
    'Cells(row,column)
    .Cells(6, 2).Value = rsCount_Missing_LLC
    .Cells(6, 3).Value = rsCount_Missing_LLC
    
    End With

Dim dlgSaveAs As FileDialog
 Dim strFilePath As String
 Dim strFileName As String
 Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)

dlgSaveAs.Show
 strFilePath = dlgSaveAs.SelectedItems("L:\DS\Data Services\Metrics\Dashboard\Dashboard_v1001.xlsx")
 strFileName = Right(strFilePath, Len(strFilePath) - InStrRev(strFilePath, "\"))
 strFilePath = Left(strFilePath, InStrRev(strFilePath, "\"))

[June7]

What does 'not work' mean - error message, wrong results, nothing happens?

[xopherira]

Nothing happens

[June7]

The file dialog does not pop up?

The code you posted looks incomplete.

[xopherira]

File dialog opens. It does not seem to be identifying with the excel WB i am writing to.

[DrGUI]

Try the InitialFileName property of the dlgSaveAs object

Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)
dlgSaveAs.InitialFileName = "L:\DS\Data Services\Metrics\Dashboard\Dashboard_v1001.xlsx"

[xopherira]

Closer with the use on InitialFileName. The File name box on save as prompt is now populated with the correct file name, but when I select save it does not actually save. Any ideas why?

[DrGUI]

I don't quite understand why you are using Access to save the file. In this case, you are trying to save an Excel file. Why not save from inside Excel? If appears to me that in order to 'save' something that 'something' must be opened. Otherwise, it doesn't know what it is you are trying to save. Maybe you need to provide more information on what exactly you are trying to accomplish.

[xopherira]

Using access to pull in some excel files and query and write to excel files. Would like to be prompted for save once this is finished.