如何使用excel文件的文件名来更改单元格列？ [英] How to use the filename of an excel file to change a column of cells?

问题描述

我正在尝试编写一个excel宏/ VBA代码，以便根据文件的标题调整列的内容。目前，我有一个宏设置来下载一个基于我输入的URL的csv文件。宏自动格式化并命名文件。我想使用文件名/标题的前5个字符自动格式化其他单元格。

I am trying to write an excel macro/VBA code to adjust a column's contents based off the title of the file. Currently, I have a macro setup to download a csv file based off a URL I type in. The macro automatically formats and names the file. I want to use the first 5 characters of the file's name/title to automatically format additional cells.

例如，文件名是48GHY_fjfdkjfe33.RHRH-msadklfjeiojo。我的文件中有一个名为name + type的列。我想结合名称行和标题，并填充字段。下面的两个图像提供了一个前后。

For example, the filename is 48GHY_fjfdkjfe33.RHRH-msadklfjeiojo. I have a column in my file called name+type. I want to combine the "name" row with the title and have it populate the fields. The two images below provide a before and after.

目前，我必须将前5个字符输入到单独的字段中，然后使用：
=48GHY& a2函数。

Currently, I have to type in the first 5 characters into a seperate field and then use the: ="48GHY"&a2 function.

有没有办法在excel / VBA中执行此操作？

Is there a way to do this in excel/VBA?

原始问题被回答了，但现在我正在寻求额外的帮助，试图把它放在一个宏。因为它仍然是同样的问题，我不认为它需要另一个职位。

The original question was answered, but now I am seeking additional help when trying to put it in a macro. Because it is still the same question, I do not think it warrants another post.

这是我正在使用的当前宏/ VBA。当我运行这个，它只显示#VALUE！在细胞中两组代码中唯一不同的是A和B列现在是J和K。

Here is the current macro/VBA I am using. When ever I run this, it just displays #VALUE! in the cell. The only thing different between the two sets of code is that the A and B columns are now J and K.

' Insert Column after name and then rename it name+type

Rows(1).Find("name").Offset(0, 1).EntireColumn.Insert
Rows(1).Find("name").Offset(0, 1).FormulaR1C1 = "name+type"

'冻结

Rows("1:1").Select
With ActiveWindow
    .SplitColumn = 0
    .SplitRow = 1
End With
ActiveWindow.FreezePanes = True

'保存文件

ActiveWorkbook.Save

'将内容添加到名称+类型列

'Add the contents to the name+type column

Range("K2").Select
ActiveCell.FormulaR1C1 = "=LEFT(MID(CELL(""filename"",RC[-1]),SEARCH(""["",CELL(""filename"",RC[-1]))+1,SEARCH(""]"",CELL(""filename"",RC[-1]))-SEARCH(""["",CELL(""filename"",RC[-1]))-1),5)&RC[-1]"
Range("K2").Select
Selection.Copy
Range("K2:K8294").Select
ActiveSheet.Paste

'自动调整所有列

Columns("A:AK").Select
Selection.Columns.AutoFit

End Sub

*另外一个注释。我修改了您在宏中提供的代码，但在运行它之后的每个单元格中，以下代码位于列k中：

*One additional comment. I modified the code you provided in the macro, but in each cell after I run it the following code is in column k:

=LEFT(MID(CELL("filename",J2),SEARCH("[",CELL("filename",J2))+1,SEARCH("]",CELL("filename",J2))-SEARCH("[",CELL("filename",J2))-1),5)&J2

*最后一个注释。文件名被保存为48GHY_fjfdkjfe33.RHRH-msadklfjeiojo＃csv.txt（可以与它有关系吗？）

*Last note. The file name is being saved as 48GHY_fjfdkjfe33.RHRH-msadklfjeiojo#csv.txt (Could that have anything to do with it?)

推荐答案

p>将它放在B2

= LEFT（MID（CELL（filename，A2）），SEARCH（[，CELL文件名，A2））+ 1，SEARCH（]，CELL（filename，A2）） - SEARCH（[，CELL（filename，A2）） - 1），5）& / code>

=LEFT(MID(CELL("filename",A2),SEARCH("[",CELL("filename",A2))+1, SEARCH("]",CELL("filename",A2))-SEARCH("[",CELL("filename",A2))-1),5)&A2

编辑

strong>

Explanation

此公式 = CELL（filename，A2）为您提供文件名。例如

This formula =CELL("filename",A2) gives you the file name. For example

C：\Users\Siddharth Rout\Desktop\ [Book1.xlsx] Sheet1

和此公式

= MID（CELL（filename，A2） ，SEARCH（[，CELL（filename，A2））+ 1，SEARCH（]，CELL（filename，A2） -1）

只从上面取出文件名，给你 Book1.xlsx

takes out just the file name from above to give you Book1.xlsx

Left（）返回文本字符串中的第一个字符或字符，您指定的字符。

The Left() returns the first character or characters in a text string, based on the number of characters you specify.

HTH

这篇关于如何使用excel文件的文件名来更改单元格列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！