VBA Excel宏通过外部文件更新列表 [英] VBA Excel Macro To Update List Via External File

问题描述

我有一张Excel表格，有两张。一个工作表被隐藏，具有名为位置的值列表。在主页上，我创建了一个从隐藏表单中拉出的下拉菜单。

如何将这些值存储在外部文件（Excel，.txt等）中）），所以我可以点击一个宏按钮（VBA），将替换/更新隐藏表单上的列表与任何/所有新的位置值将存储在外部文件中

解决方案

我相信这是你正在寻找的：

  Dim intPointer as Integer 
 Dim strFileToImport as String 
 Dim strLine as String 
 
 intPointer = FreeFile（）
打开strFileToImport对于输入访问读取锁读为#intPointer 
直到EOF（intPointer）
行输入#intPointer，strLine 
 SheetWithLocations.Cells（lngRow，1）.Value2 = strLine 
 lngRow = lngRow + 1 
循环
  

它打开一个名为strFileToImport的外部txt文件，并从txt文件逐行读取并将其写入SheetWithLocations。 / p>

I have an excel sheet that has two sheets. One sheet is hidden that has a list of values called "Location." On the main sheet I have created a drop down menu that pulls from the hidden sheet.

How can I store these values in an external file (Excel, .txt, etc.) so I can hit a macro button (VBA) that will replace/update the list on the hidden sheet with any/all new Location values that will be stored in an external file?

解决方案

I believe this is what you are looking for:

Dim intPointer as Integer
Dim strFileToImport as String
Dim strLine as String

intPointer = FreeFile()
Open strFileToImport For Input Access Read Lock Read As #intPointer
Do Until EOF(intPointer)
    Line Input #intPointer, strLine
    SheetWithLocations.Cells(lngRow, 1).Value2 = strLine
    lngRow = lngRow + 1
Loop

It opens an external txt file called strFileToImport and reads row by row from the txt file and writes it into the SheetWithLocations.

这篇关于VBA Excel宏通过外部文件更新列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！