VBA Loop To Import Changing File Names

0 votes

If a File Amend.xls file already exists, the code I need will only copy the data from that file and paste it into its own tab. I am looking for a code to search a directory for specific file names with a changing number at the end of the name (File 1.xls, File 2.xls, File 3.xls, etc.) and stack the data within the reports on top of each other into a tab. 1, 2, 3, etc. or Amend are the only parts of the file that can be changed. Each file ends with.xls.

I've gotten this far:

Sub SaveFile()

Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet
Dim File As String
Dim wsCopy As Worksheet
Dim wsCopy2 As Worksheet
Dim wsCopy3 As Worksheet
Dim wsPaste As Worksheet

' For this part I am looking to have the file name constant as "File_" and then have the code search for files with the numbers 1,2,3,4, etc. instead of hardcoding in the file name 

File = "L:\Main\Code\"

Set wsCopy = File & wb.Sheets("Main").Range("C6") 'this value is "File 1.xls"
Workbooks.Open Filename:=wsCopy, ReadOnly:=True

Set wsCopy2 = File & wb.Sheets("Main").Range("C7") 'this value is "File 2.xls"
Workbooks.Open Filename:=wsCopy2, ReadOnly:=True

Set wsCopy3 = File & wb.Sheets("Main").Range ("C8") 'this value is "File Amend.xls"
Workbooks.Open Filename:=wsCopy3, ReadOnly:=True

Set wb = Workbooks.Add
Set wsPaste = wb.Sheets(1)

If Dir(wsCopy) = True Then
wsCopy.Range ("A:I").Copy
wsPaste.Cells.PasteSpecial Paste:=xlPasteValues

If Dir(wsCopy2) = True Then
wsCopy2.UsedRange.Offset(1,0).SpecialCells(xlCellTypeVisible).Copy
wsPaste.Cells (Rows.Count, "A").End(x1Up).Offset (1, 0).PasteSpecial Paste: xlPasteValues

If Dir(wsCopy3) = True Then
wsPaste.Cells.ClearContents
wsCopy3.Range("A:I").Copy
wsPaste.Range("Al").PasteSpecial Paste:=xlPasteValues

End Sub

Example

Jan 12 in Others by Kithuzzz
• 28,960 points
54 views

1 answer to this question.

0 votes

You can use a FOR loop and a separate "File" constant to search for filenames with different integers. specific examination of File Amend Before the aforementioned code, place the file xls as follows:

If Dir ("L:\Main\Code\File_Amend.xls" = True then
   ...
Else
   File = "L:\Main\Code\File_"
   For i = 1 to 99
      wsCopy = File & i & ".xls"
      ...
   Next i
End if
answered Jan 12 by narikkadan
• 53,920 points

Related Questions In Others

0 votes
1 answer

How to import excel file in Oracle SQL live

Hello, there are a few steps You'll ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 22,960 points
740 views
0 votes
1 answer

How to get the excel file name / path in VBA

Use FullName, for example: strFileFullName = ThisWorkbook.FullName ...READ MORE

answered Dec 23, 2022 in Others by narikkadan
• 53,920 points
65 views
0 votes
1 answer

How to use relative names in Excel VBA

It appears you are looking for Range.Offset() http://msdn.microsoft.com/en-us/library/office/ff840060%28v=office.15%29.aspx However, you ...READ MORE

answered Dec 24, 2022 in Others by narikkadan
• 53,920 points
47 views
0 votes
1 answer

How to increment the Range of a For Each loop - Excel VBA

Your formula seems to sum 1 single ...READ MORE

answered Jan 7 in Others by narikkadan
• 53,920 points
82 views
0 votes
1 answer

Generate VCards from Excel using VBA

Solution  Create a class called CContact with getters ...READ MORE

answered Nov 21, 2022 in Others by narikkadan
• 53,920 points
61 views
0 votes
1 answer

Is there a hierarchy inferring algorithm available in python?

Convert the list of values to the ...READ MORE

answered Jan 15 in Others by narikkadan
• 53,920 points
43 views
0 votes
1 answer

Trying to run different macros based on value of cell in a range

This demonstrates one approach to loop through ...READ MORE

answered Jan 19 in Others by narikkadan
• 53,920 points
38 views
0 votes
1 answer

How to make a auto counter to print pages

This will loop through all of those ...READ MORE

answered Jan 19 in Others by narikkadan
• 53,920 points
39 views
0 votes
1 answer

Convert a single png file to jpg in vba

Try this code: Sub ConveretPNGToJpg() ...READ MORE

answered Oct 16, 2022 in Others by narikkadan
• 53,920 points
275 views
0 votes
1 answer

How To Use VBA To Share Excel File With Fellow Office User?

PowerApps were referenced; if you have a ...READ MORE

answered Oct 28, 2022 in Others by narikkadan
• 53,920 points
156 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP