获取正确的默认保存名称并在VBA中使用空格保存目录 [英] Getting correct default save name and save directory with spaces in VBA

问题描述

我有很多名称不同的excel模板.其中之一称为 griep-weerstand v4.xlsb .但是我的问题是关于所有模板的.

I have a lot of excel templates with varying names. One of them is called griep-weerstand v4.xlsb. But my question is about all the templates.

我想将文件名集成到保存脚本中，该脚本设置默认保存目录和默认保存名称.两者的名称中都有空格.添加正确的引号后，正确设置了默认的保存目录，但是，我一直在努力将工作簿名称添加到脚本中.我尝试了几种方法，但到目前为止都没有奏效.

I want to integrate the filename into a save script which sets the default save directory and default save name. Both have spaces in the name. After adding the correct number of quotes, the default save directory is set correctly, however, I keep struggling with adding the workbookname to the script. I tried several things and none of them has worked so far.

默认保存目录为: M:\ Commercie \ Marktdata \ IRi \ Segment ontwikkeling

默认文件名(在此示例中)为: griep-weerstand v4.xlsb

the default file name (in this example) is: griep-weerstand v4.xlsb

以下是我最后尝试的内容:

Below is what I tried last:

Sub save_workbook_name()
Dim workbook_Name As Variant
Dim location As String
Dim workbookname As String
Dim workbookdirectory As String
Dim correctfilename As Variant

workbookname = ActiveWorkbook.Name
workbookdirectory = "M:\Commercie\Marktdata\IRi\Segment ontwikkeling\"

correctfilename = """M:\Commercie\Marktdata\IRi\Segment ontwikkeling\" & workbookname & """"
workbook_Name = Application.GetSaveAsFilename(fileFilter:="Excel binary sheet (*.xlsb), *.xlsb", initialfilename:=correctfilename)

If workbook_Name <> False Then

    ActiveWorkbook.SaveAs Filename:=workbook_Name, FileFormat:=50

End If
End Sub

我认为将工作簿名称作为字符串读取，并在组合的initialfilename中添加正确的引号即可解决问题.

I thought reading the workbookname as a string and adding the right number of quotes into the combined initialfilename would do the trick.

当我添加一个显示合并结果的消息框时，我得到正确的路径和名称:

When I add a message box displaying the combined result, I get the correct path and name:

但是我继续在正确的目录中保存另存为对话框屏幕，但是在getsaveasfilename行中使用此文件名时却没有文件名.

But I keep on having the save as dialog screen in the correct directory but without a filename when using this in the getsaveasfilename line.

如何获取目录和文件名的组合以与getsaveasfilename一起使用?还是应该在activeworkbook.saveas脚本中使用它?

How can I get the combination of the directory and filename to work with the getsaveasfilename? Or should I just use it in the activeworkbook.saveas script?

推荐答案

似乎有点复杂-您是否尝试过更简单的方法:

It seems a little complicated - have you not tried the simpler:

workbook_Name = Application.GetSaveAsFilename(fileFilter:="Excel binary sheet (*.xlsb), 
*.xlsb", initialfilename:=workbookdirectory & ActiveWorkbook.Name)

基于此处示例的完整代码:..它完全可以为您工作(使用包含空格的文件名进行测试)

full code based on your example here: .. this works for me exactly as you want it (tested with a filename containing spaces)

Sub save_workbook_name()
Dim workbook_Name As Variant
Dim location As String
Dim workbookname As String
Dim workbookdirectory As String
Dim correctfilename As Variant

'workbookname = ActiveWorkbook.Name
workbookdirectory = "C:\Users\myusername\folder with spaces too\"

'correctfilename = """M:\Commercie\Marktdata\IRi\Segment ontwikkeling\" & workbookname & """"
workbook_Name = Application.GetSaveAsFilename(fileFilter:="Excel binary sheet (*.xlsb), *.xlsb", InitialFileName:=workbookdirectory & ActiveWorkbook.Name)

If workbook_Name <> False Then

    ActiveWorkbook.SaveAs Filename:=workbook_Name, FileFormat:=50

End If
End Sub

这篇关于获取正确的默认保存名称并在VBA中使用空格保存目录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！