PDA

View Full Version : [SOLVED] Need help with this VBA code



Rafea1979
12-28-2015, 06:41 PM
Hi

I need VBA code which will copy one value from excel files then paste all values with the name of the file taken from in one master excel file.

Example on what I need

File name Value
Book1 1.2
Book2 1.5
Book3 1.6

I have VBA codes to copy the values however I do NOT know how to copy and paste the file names! I need help to find the code to copy the file name next to its value.

Thank you

Rafea1979
12-28-2015, 06:57 PM
Here is the code

Sub LoopAllExcelFilesInFolder()

'PURPOSE: To loop through all Excel files in a user specified folder and perform a set task on them

Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog
Dim erow

'Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
'Retrieve Target Folder Path From User
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
With FldrPicker
.Title = "Select A Target Folder"
.AllowMultiSelect = False
If .Show <> -1 Then GoTo NextCode
myPath = .SelectedItems(1) & "\"
End With

'In Case of Cancel
NextCode:
myPath = myPath
If myPath = "" Then GoTo ResetSettings


'Target File Extension (must include wildcard "*")
myExtension = "*.xls"

'Target Path with Ending Extention
myFile = Dir(myPath & myExtension)

i = 1

'Loop through each Excel file in folder
Do While myFile <> ""
'Set variable equal to opened workbook
Set wb = Workbooks.Open(Filename:=myPath & myFile)

'Copy D-spacing C4 cell
wb.Worksheets(1).Range("C4:C4").Copy

'Close Activeworkbook
ActiveWorkbook.Close

' Paste C4 into another master excel file
erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row + i
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 1))

i = i + 1

'Save and Close Workbook
' wb.Close SaveChanges:=True

'Get next file name
myFile = Dir
Loop


'Message Box when tasks are completed
MsgBox "Task Complete!"

ResetSettings:
'Reset Macro Optimization Settings
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

SamT
12-28-2015, 08:29 PM
Try replacing the loop in that code with this loop
'Loop through each Excel file in folder
Do While myFile <> ""
'Set variable equal to opened workbook
Set wb = Workbooks.Open(Filename:=myPath & myFile)

'Place values in Columns A and B of first empty Row
With Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp)
.Offset(1, 0).Value = wb.Name
.Offset(1, 1).Value = wb.Worksheets(1).Range("C4").Value
End With

'Close Opened Workbook
wb.Close SaveChanges:=True

'Get next file name
myFile = Dir
Loop

Rafea1979
12-28-2015, 09:43 PM
Thank you however I did what you have suggested and still getting the below errors

Sorry I'm NOT a programmer but I have been trying to create this VBA code to analyze some data.

1505715058

SamT
12-29-2015, 01:31 AM
First, make sure that you have a worksheet named "Sheet1"

Then try replacing the "A" after Rows.Count with 1

(Replace "A" with quotes, with a 1 without quotes.)

BTW, aren't you going to close the "wb" workbook when you are done with it and before you open a new one?

You really should, or you will have dozens of open workbooks.

Rafea1979
12-29-2015, 02:32 AM
Thank you very much for the help however I have two issues
1. All excel files I had do NOT have sheet1 so I should change all into sheet1
2. It worked after I have changed few files to sheet1 but it copied the C4 and file name in the same file.

I really thank you for your help but How can I extract the value into a separate excel file having only values and file names.

Thank you

SamT
12-29-2015, 10:02 AM
It is very important in VBA, (and in all other codes,) to say exactly what you mean.

For example: Sheet1, Worksheets(1), and Worksheets("Sheet1") mean three different things. I can give you a workbook that has three Worksheets in it and use each of those terms to reference a different sheet. OR they can all reference the same sheet. You must understand your Workbook to understand which term to use to access which sheet you wish.

The workbook with the code in it must have a Worksheet with the Tab Name "Sheet1"

All the Workbooks with the Values in them must have only one Worksheet in them, in order for this code to work as is. It does not matter what the Tab Name of the Worksheet is if there is only one Worksheet. That is why the Code is using Worksheets(1) for the Value sheet.





15065


The so called Tab Name is the name of the sheet as seen on the sheet tabs in Excel.

Note in the image that I have the name Sheet1 in two places! One is the Code Name of the Worksheet ("Current Summary"). The Other is the Tab Name of the (Sheet) Object Sheet2. Since I have many Worksheets (Sheets), If I use Sheets(1) in my code, I don't know which sheet I will get.

Sheets and Worksheets are two different things, but all Worksheets are also in Sheets. Not all Sheets are in Worksheets. (There are also Sheets called Charts, which are not Worksheets.)

Rafea1979
12-29-2015, 04:08 PM
This is an excellent explanation.

I really appreciate your time and help.

It works with me.

Sweet :)

SamT
12-29-2015, 08:20 PM
Does that mean that your code is working now?

You can use the "Thread Tools" menu above the posts to mark this thread "Solved."

Rafea1979
12-29-2015, 08:27 PM
Yes
it is working fine and also I have learned much more about VBA.

SamT
12-29-2015, 08:29 PM
:thumb