Is there a way to change VBA code to open a specific folder instead of file explorer for importing files?

huangapple go评论50阅读模式
英文:

Is there a way to change VBA code to open a specific folder instead of file explorer for importing files?

问题

抱歉,但我只能回答你的翻译请求。请提供需要翻译的文本。

英文:

This problem might have an easy solution, but I cannot get it to work. Id like to open a specific folder to run the following section of the VBA code:
Sub ImportText3()

Dim ThisLine As Variant
Dim fileFilterPattern As String
Dim n As Long

' Just look at text files
'fileFilterPattern = "Text Files *.txt,*.txt"
fileFilterPattern = "Text Files (*.txt; *.csv),*.txt;*.csv" 'use this line for general txt file selection
ThisLine = Application.GetOpenFilename(fileFilterPattern)

If ThisLine = False Then
    MsgBox "Did not select files from Josue`s folder."
    Exit Sub
End If

Application.ScreenUpdating = False

Open ThisLine For Input As #1`

Instead of opening the file explorer, I want it to open a specific folder, which is what excel is doing.

I tried using fileFilterPattern = "C:\Users\jcriollo\Documents\Bronx\Raw Data\05.23" as my file path insteasd of having fileFilterPattern = "Text Files (*.txt; *.csv),*.txt;*.csv", but the code is giving me bugs. Is there a simple way to change this?

答案1

得分: 1

以下是翻译好的部分:

1. 更改Excel的工作文件夹。

在显示对话框之前,执行以下代码(使用您所需的文件夹路径):

ChDir "C:\YourFolder\"

请注意,这将更改Excel会话的工作文件夹。

2. 使用 FileDialog

尝试以下代码:

Set FileDialog = Application.FileDialog(msoFileDialogOpen)
FileDialog.InitialFileName = "C:\YourFolder\"
result = FileDialog.Show

If (result = -1) Then MsgBox FileDialog.SelectedItems(1)

您需要根据自己的需求进行调整 - 文档在此处

第二种方法的主要优点是它仅影响显示的对话框,不影响整个Excel会话(即Excel的工作文件夹保持不变)。

英文:

Here are two possible ways:

1. Change Excel's working folder.

Before you display the dialog box, execute this line (using your desired folder path):

ChDir "C:\YourFolder\"

Note that this will change Excel's working folder for the session.

2. Use a FileDialog.

Try this:

Set FileDialog = Application.FileDialog(msoFileDialogOpen)
FileDialog.InitialFileName = "C:\YourFolder\"
result = FileDialog.Show

If (result = -1) Then MsgBox FileDialog.SelectedItems(1)

You will need to adapt this to your needs - documentation is here.

The main advantage of the second approach is that it affects only the dialog box displayed, not the whole Excel session (i.e. Excel's working folder remains unchanged).

huangapple
  • 本文由 发表于 2023年5月30日 02:09:29
  • 转载请务必保留本文链接:https://go.coder-hub.com/76359497.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定