Using Power Query and VBA To Update File Names In Folder

[Stacker]

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

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

 

[Alex Blakenburg]

Can you start with this:

• Folder name is ending in a ")" should end in a "\"
  S/be
  

  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:

            Debug.Print "old"; strFolder & strFileNameExisting
            Debug.Print "new"; strFolder & strFileNameNew

And show us what it prints in the immediate window.

 

[Stacker]

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 A22 and downwards . I want worksheets CYHT copied and then the contents of MHFT copied directly underneath.

 

[Alex Blakenburg]

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 A22 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 A22 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]

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]

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.