Excel importing data from another Excel file using VBA

0 votes

I want to use a For loop to simultaneously import data from ten different Excel spreadsheets. Column A of the sheet "Folders" lists the names of these ten more spreadsheets.

To document the procedures involved in importing data from a file called TEST FILE, I created a macro. How can I modify this so that each loop uses a different file name, each of which is saved in the variable folderName?

Dim numFolders As Integer
Dim folderPosition As Integer
Dim folderName As String

numFolders = 10

For folderPosition = 1 To numFolders

folderName = Sheets("Folders").Range("A" & folderPosition).Value

ActiveWorkbook.Queries.Add Name:="TEST_FILE", Formula:= _
    "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.Workbook(File.Contents(""C:\Location\TEST FILE.XLS""), null, true)," & Chr(13) & "" & Chr(10) & "    #""TEST_FILE1"" = Source{[Name=""TEST_FILE""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(#""TEST_FILE1"", [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Name"", type text}, {""Type""" & _
    ", type text}, {""Description"", type text}, {""Format"", type text}, {""Created"", type date}, {""Records"", type text}, {""List?"", type logical}, {""Created by"", type text}, {""Last run"", type date}, {""Last changed"", type date}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
    "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=2012_Appeals;Extended Properties=""""" _
    , Destination:=Range("$A$1")).QueryTable
    .CommandType = xlCmdSql
    .CommandText = Array("SELECT * FROM [TEST_FILE]")
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .ListObject.DisplayName = "_TEST_FILE"
    .Refresh BackgroundQuery:=False
End With
Application.CommandBars("Queries and Connections").Visible = False

Next
Apr 7 in Others by narikkadan
• 63,040 points
134 views

1 answer to this question.

0 votes

Refer to the file variables folderName and folderlocation where "TEST FILE" and the location of the test file are hardcoded, as seen below:

Dim numFolders As Integer
Dim folderPosition As Integer
Dim folderName As String

numFolders = 10

For folderPosition = 1 To numFolders

folderName = Sheets("Folders").Range("A" & folderPosition).Value
folderLocation = "C:\Location\"+ folderName

ActiveWorkbook.Queries.Add Name:=folderName, Formula:= _
    "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.Workbook(File.Contents(""" + folderLocation + """), null, true)," & Chr(13) & "" & Chr(10) & "    #""TEST_FILE1"" = Source{[Name="""+ folderName + """]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(#""TEST_FILE1"", [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Name"", type text}, {""Type""" & _
    ", type text}, {""Description"", type text}, {""Format"", type text}, {""Created"", type date}, {""Records"", type text}, {""List?"", type logical}, {""Created by"", type text}, {""Last run"", type date}, {""Last changed"", type date}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
    "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=2012_Appeals;Extended Properties=""""" _
    , Destination:=Range("$A$1")).QueryTable
    .CommandType = xlCmdSql
    .CommandText = Array("SELECT * FROM [" + folderName + "]")
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .ListObject.DisplayName = "_"+ folderName 
    .Refresh BackgroundQuery:=False
End With
Application.CommandBars("Queries and Connections").Visible = False

Next
answered Apr 7 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

How to convert data from txt files to Excel files using python

Hi , there are few steps to ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,630 points
10,365 views
0 votes
1 answer

Excel VBA if file closed, then open and paste, else just paste data

Slightly re-worked to add full workbook/sheet qualifiers ...READ MORE

answered Sep 21, 2022 in Others by narikkadan
• 63,040 points
334 views
0 votes
1 answer

Removing specific rows in an Excel file using Azure Data Factory

Under the 'Source' tab, choose the number ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,040 points
1,178 views
0 votes
1 answer

Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,040 points
754 views
0 votes
1 answer

Unable to import data in excel from another website using VB code

Replace : Set ieTable = ieDoc.all.Item("report-table") With: Set ieTable = ...READ MORE

answered Sep 21, 2022 in Others by narikkadan
• 63,040 points
275 views
0 votes
1 answer

Runtime error 438 while importing data in excel from secured website using VBA

Replace With ieDoc.forms(0) .userType.Value = "1" ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,040 points
345 views
0 votes
1 answer

Excel VBA - Trouble importing WhatsApp chat history files into an Excel sheet

Since the OpenText method isn't working for ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 63,040 points
232 views
0 votes
1 answer

How can I preserve the format while exporting data from excel to evernote

The contents for an Evernote note are ...READ MORE

answered Jan 5 in Others by narikkadan
• 63,040 points
111 views
0 votes
1 answer

Copy data with filter applied using Excel VBA

Try this: Private Sub CommandButton1_Click() Dim ...READ MORE

answered Mar 19 in Others by Kithuzzz
• 38,010 points
108 views
0 votes
1 answer

VBA Excel get data from Access database Error

Try this: Sub getdata() Const ...READ MORE

answered Mar 19 in Others by Kithuzzz
• 38,010 points
85 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