VBA Access SaveAS

[antonio.manoj.derose]

Hi Folks,

Good Moning to you all, how would I save the file, to the destination that I want to, in essence at the time of saving the save dilaog window only should open.

I am getting other windows, as I am passing the file as .xlt file.

Thanks,

Antonio

 

[JHB]

Which other windows?
What do you want to save, from where do you want to save it and how do you want to save it?
Is it from code, then show you code.

 

[antonio.manoj.derose]

Set xlApp = CreateObject("Excel.Application")
Set xlbook = xlApp.Workbooks.Open(templatefile)
Set xlWrksht = xlbook.Sheets("Already Paid")

Set rsIn = CurrentDb().OpenRecordset("Mobile")

Set rsIn = CurrentDb().OpenRecordset("Mobile")

If rsIn.RecordCount > 0 Then

rsIn.MoveLast
rsIn.MoveFirst

rowIdx = 2

With xlWrksht

While Not rsIn.EOF

For i = 1 To rsIn.Fields.Count ' - 1

'If rs.Fields(i).Name <> "PremiseCode" Then
Debug.Print rsIn.Fields(i - 1).Value
.Cells(rowIdx, i) = rsIn.Fields(i - 1).Value
'End If

Next i

rsIn.MoveNext
rowIdx = rowIdx + 1

Wend
End With


End If

xlbook.SaveAs (C:\)

xlbook.Close
xlApp.Quit

rsIn.Close
Set rsIn = Nothing

the above does not ask me in opening a save as dialg box, where I want to save the file at my liking, in essence, I want to see the file dialog window for save as

 

[JHB]

The below should work:

Dim fName
..
..
fName = xlApp.GetSaveAsFilename 
..
..

 

[antonio.manoj.derose]

Hi JHB,

Thank you for the response, would you please send me the whole set of coding, cause when I try to save it, it pops us with the dialog box, and it does not allow me to select the .xls from the drop down, as the all files is fixed.

Would you please help me to save it as an excel file.

Thanks,

Antonio

 

[JHB]

You type in the different file-type you want to choose between, in the fileFilter.
If you can't remember them, open Excel, choose "Save As", then you can see them all.

Do
  fname = xlApp.GetSaveAsFilename(fileFilter:="Microsoft Excel Files (*.xls), *.xls, Text Files (*.txt), *.txt")
Loop Until fname <> False Or msoButtonSetOkCancel = Cancel
If Not fname = False Then
  xlbook.SaveAs FileName:=fname
End If

When I have problem remember how code has to look like in Excel, I open the code pane in Excel, and from there open the Help-file.
And please read my signature, if the above helps you.

 

[antonio.manoj.derose]

Hey JHB,

Seem like working fine, thank you for that, except for one occasion, where I had to close the excel template as well, once I have saved my own file at my liking.

Thanks,

Antonio

 

[JHB]

Have you removed the line xlbook.SaveAs (C:\)?

 

[antonio.manoj.derose]

Yes I have removed it.