Happy Friday, all. I'm one of those people who only knows enough VBA to be dangerous. I have a query in my Access database and I would like to allow the users to save it using a Save As dialog box. Below is the code I am using. I like it and it works, but I have one problem I'm hoping I can easily fix.
When I open the Excel spreadsheet after having saved it, the cells that were null in the Access query act like spaces. For example, when I have my cursor in one of the column header cells and hit Ctrl+down arrow, I go to the very bottom of the list. This is what happens even if there are cells in the column that are empty. I would like it to instead go to the next value in the column instead of all the way to the end. At least that is how I like to use it. Perhaps it's better the way it's actually working? I'm open to trying to undertsand the benefits of both, but was wondering if there was a way to make it act like it would if I manually exported the query to Excel. Not to mention, I like the formatting better this way as well, but asking my users to manually export the file isn't an option. Perhaps OutputTo isn't the best idea either. Thanks for any and all assistance!!
-----
Dim fd As FileDialog
Dim Title As String
Dim vrtSelectedItem As Variant
Set fd = Application.FileDialog(msoFileDialogSaveAs)
With fd
.AllowMultiSelect = False
.Title = "Save File"
.initialFilename = ".xls"
If .Show = True Then
DoCmd.OutputTo acOutputQuery, "Report", acFormatXLS, .SelectedItems(1)
Else
MsgBox "File Not Saved", vbOKOnly, "File Not Saved"
End If
End With