VBA-将上载的.csv文件名放入某些工作表的单元格中 [英] VBA - Place uploaded .csv file name into cell on certain sheet

问题描述

我想知道是否有一种方法可以提取选定的.csv文件并将其名称放入摘要"表中的单元格中.这是上传.csv文件的代码:

I was wondering if there is a way to take the .csv file that is selected and place the name into a cell on the 'Summary' sheet. Here is the code to upload the .csv file:

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Input Raw Data") 'set to current worksheet name

strFile = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Please select text file...")

With ws.QueryTables.Add(Connection:="TEXT;" & strFile, 
Destination:=ws.Range("A1"))
     .TextFileParseType = xlDelimited
     .TextFileCommaDelimiter = True
     .Refresh
End With

我不确定是否有办法做到这一点，谢谢.

I am not sure if there is some way to do that, thanks in advance.

推荐答案

将解决方案从我在注释中提供的链接应用于问题(您需要添加对Microsoft Scripting Runtime的引用(IDE中的工具">引用") ):

Applying the solution from the link I provided in the comment to the question (you would need to add a reference to Microsoft Scripting Runtime (Tools > References in the IDE):

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Input Raw Data") 'set to current worksheet name

strFile = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Please select text file...")

With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
    .TextFileParseType = xlDelimited
    .TextFileCommaDelimiter = True
    .Refresh
End With


Dim fso as new FileSystemObject
Dim fileName As String
fileName = fso.GetFileName(strFile)
Worksheets("Summary").Range(Your Range Here).Value = fileName

这篇关于VBA-将上载的.csv文件名放入某些工作表的单元格中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！