Using Power Query and VBA To Update File Names In Folder

Stacker

New Member
Joined
Jul 11, 2021
Messages
47
Office Version
  1. 365
Platform
  1. Windows
So I wanna update file names using power Query. I have created a table with my old file name and new file name using power query. However, I wanna know how I can automate this using Power Query and Excel. I used this guide How to rename all the files in a folder using Excel VBA? - Free Excel Tips & Tricks | Free Excel VBA Code however when I use the macro all i get is an error. Here is the code I used

VBA Code:
Sub RenameAllFilenamesInAFolder()

Dim intRowCount As Integer

Dim intCtr As Integer

Dim strFileNameExisting As String

Dim strFileNameNew As String

Dim strFolder As String

'Set the folder path

strFolder = "C:\Users\Username\Desktop\FolderWithUnformatedFiles)"

With Sheet1

'Find the total rows count in the sheet

'This will be the last non-blank cell in column A...

intRowCount = .Cells(.Rows.Count, "A").End(xlUp).Row

'Loop through from the 2nd row (1st row is Heading)

'till the total rows in the sheet

For intCtr = 2 To intRowCount

With Sheet1

'Find the total rows count in the sheet

'This will be the last non-blank cell in column A...

intRowCount = .Cells(.Rows.Count, "A").End(xlUp).Row

'Loop through from the 2nd row (1st row is Heading)

'till the total rows in the sheet

For intCtr = 2 To intRowCount

'Get the existing filename from the cell

strFileNameExisting = .Range("A" & intCtr)

'Get the new filename from the cell

strFileNameNew = .Range("B" & intCtr)

'Rename the file

Name strFolder & strFileNameExisting As strFolder & strFileNameNew

Next intCtr

End With

'Display an appropriate message, once complete

MsgBox "All files renamed successfully!", _

vbInformation , "All files renamed"

End Sub
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,732
Office Version
  1. 365
Platform
  1. Windows
Can you start with this:
  • Folder name is ending in a ")" should end in a "\"
    S/be
    VBA Code:
    strFolder = "C:\Users\Username\Desktop\FolderWithUnformatedFiles\"
  • With Sheet1 appears once before the For intCtr AND again after the For intCtr statement.
    Remove the one after the For intCtr statement aka remove the one inside the loop
    Note: The End With is outside the loop after the Next statement which is consistent with keeping the one before the For statement
If that gets rid of the error message but it still doesn't work then before the Name statement enter these 2 lines:
VBA Code:
            Debug.Print "old"; strFolder & strFileNameExisting
            Debug.Print "new"; strFolder & strFileNameNew
And show us what it prints in the immediate window.
 
Solution

Stacker

New Member
Joined
Jul 11, 2021
Messages
47
Office Version
  1. 365
Platform
  1. Windows
I was wondering is it possible to create a macro to copy and paste the files from the two tables. I wanna copy all of the tables content from A2:eek:2 and downwards . I want worksheets CYHT copied and then the contents of MHFT copied directly underneath.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,732
Office Version
  1. 365
Platform
  1. Windows
I was wondering is it possible to create a macro to copy and paste the files from the two tables. I wanna copy all of the tables content from A2:eek:2 and downwards . I want worksheets CYHT copied and then the contents of MHFT copied directly underneath.
You will need to log this as a separate thread.
You will need to be much more descriptive though.
eg
What is this referring to "to copy and paste the files from the two tables" ?
What is this meant to say - "from A2:eek:2 and downwards" ?
XL2BB of Source and Destination (including expected results) - macros need to know sheet names and table names you are using.
Include any existing code you already have.

PS: If you are just combining data - why not just use Power Query ?
 

Stacker

New Member
Joined
Jul 11, 2021
Messages
47
Office Version
  1. 365
Platform
  1. Windows
Like if i went ctrl +Right, Ctrl + down, ctrl+C, Ctrl+V

I actually did use power query and it works but there were some weird random errors like a random extra blank line that i removed at the end. Not sure why. When i performed conditional formatting to compare my two tables, manual vs power query and it highlighted differences despite when i brought them up they were the same. **** confused me.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,732
Office Version
  1. 365
Platform
  1. Windows
The differences could be just data types eg if when you copied it you had it a numbers but in PQ you made it text.
Like I said you need to log it as a new thread possible even 2.
For the VBA one perhaps just record the code first and then ask what you want changed.
If you are using PQ you can do a thread to trouble shoot the problem but people need an XL2BB of some data you are using as Input and you need to show what is wrong with the output you are getting and what it should look like.
 

Forum statistics

Threads
1,141,572
Messages
5,707,166
Members
421,495
Latest member
jono_oh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top