从VB论坛重新发布 - Excel OPEN命令问题 [英] Repost from VB Forum - Excel OPEN Command question

问题描述

我想知道如何使用VBA编写excel代码来打开多个文件并从这些文件中挑选数据并将它们合并到主文件中。问题是每个月这些文件的文件路径都会改变。有没有办法让宏自动更新文件路径？

我有多个文件，我需要每个月提取数字。它们位于常规文件夹中的不同位置。在主文件完成后的每个月，我们复制整个文件夹并将其重命名为不同的月份。例如，4月份我们有4月文件夹，其中包含此文件夹中的文件夹中的多个数据excel文件以及合并文件。在5月的下个月，我们复制文件夹并将其重命名为MAY并开始在各个文件中累积MAY数据。最后，我希望宏知道文件仍然与当前月份文件夹位于同一位置，而不是4月文件夹。

任何帮助将不胜感激。

解决方案

这是一个简单的方法：

 Private Sub Change_Month（）
 
 Dim sDir 
 Dim sMonth 
 
 Dim sPath As String 
 sPath =" ; C：\temp\" 
 sMonth = MonthName（月（现在），True）
 
 
 sDir = Dir（sPath& sMonth&" \ * .xls"）
 Do Until Len（sDir）= 0 
 MsgBox sPath& sDir 
 
'在这里你可以打开工作簿并开始处理
 Workbooks.Open sPath& sDir 
 
 sDir = Dir 
 Loop 
 
 End Sub 
 
 

以上使用当月的缩写形式作为文件夹名称

即，

c：\ temp \ Maye \
c：\ temp \ Apr \等

如果您想要完整表格，请使用

月份名称（月（现），假）

欢呼声<登记/> Shasur

Hi,

I would like to know how to code for excel using VBA to open multiple files and pick out data from those files and consolidate them in a main file. The problem is that every month the file path to those files will change. Is there a way to have the macro automatically update the file paths?

I have multiple files in which i need to pull numbers from each month. Theyre in different locations within a general folder. Every month after the main file is complete we copy over the entire folder and rename it a different month. For example, April month we have the April folder and in it has several data excel files in folders within this folder as well as the consolidation file. The next month May, we copy over the folder and rename it MAY and start accumulating MAY data in the individual files. In the end I would like the macro to know that the files are still in the same location as the current months folder and not in the April folder.

Any help would be appreciated.

解决方案

Here is a simple way of doing it:

Private Sub Change_Month()

   Dim sDir
    Dim sMonth

    Dim sPath As String
    sPath = "c:\temp\"
    sMonth = MonthName(Month(Now), True)
    
        
    sDir = Dir(sPath & sMonth & "\*.xls")
    Do Until Len(sDir) = 0
        MsgBox sPath & sDir
        
        ' Here you can open the workbook and start processing
        Workbooks.Open sPath & sDir
        
        sDir = Dir
    Loop
    
End Sub

The above uses the abbreviated form of the current month as folder name

i.e.,

c:\temp\May\
c:\temp\Apr\ etc

IF you want the full form, please use

MonthName(Month(Now), False)

Cheers
Shasur

这篇关于从VB论坛重新发布 - Excel OPEN命令问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！