My problem is that I need to open some excel files using VBA (for excel 2007) and extract the data. All the files I want to open are called "profit for January.xlsx", "profit for February.xlsx", and so on with only the month name changing, so I think I want to open a file called "profit for*". There is another file in the folder called "total revenue.xlsx" that I do not want to open.

If possible, I need the code to extract the data from the files in the folder, wherever the folder may be because I am sending this code to my colleagues to put into their own folders, which have the same file name formats etc but different paths.

I have the code to extract the data, which works, but it either imports all the data in the folder or none at all!

Any help on this would be much appreciated as I am an intern trying to get his foot in the door, and this would be quite a big break for me!

Further Information

So far I have the code below (I haven't included the dim's because I felt they may be unnecessary?), which I have drawn from other websites. I'm also finding that, in trying to open all the files in the folder, it is trying to open itself! If anyone could suggest how to improve this, I would be very grateful. I haven't been using VBA for very long, and have been finding this assignment quite tough!

The error box that comes up sometimes says that I need an 'object' for the variable sfilename, and I'm not sure how to do that without messing up another part of the code.

sub import data ()
ChDir ThisWorkbook.Path Set objFSO = CreateObject("Scripting.FileSystemObject") Set sfolder = objFSO.GetFolder(ThisWorkbook.Path) For Each sfilename In sfolder.Files If sfilename <> "Total Revenue.xlsx" Then Workbooks.Open Filename:= _ sfilename *'open the file* Set sfilename = ActiveWorkbook *'set the file name as sfilename, so the single piece of code will work with the copy-loop* b = Sheets.Count *'for the data-import loop* Call ImportData *'call in the loop* sfilename.Close *'close the file* End If Next
end sub

2 Answers

So far I have the code below (I haven't included the dim's because I felt they may be unnecessary?), which I have drawn from other websites. I'm also finding that, in trying to open all the files in the folder, it is trying to open itself! If anyone could suggest how to improve this, I would be very grateful. I haven't been using VBA for very long, and have been finding this assignment quite tough!

The error box that comes up sometimes says that I need an 'object' for the variable sfilename, and I'm not sure how to do that without messing up another part of the code.

Many thanks and kindest regards, Mark

sub import data ()

ChDir ThisWorkbook.Path

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set sfolder = objFSO.GetFolder(ThisWorkbook.Path)
For Each sfilename In sfolder.Files If sfilename <> "Total Revenue.xlsx" Then Workbooks.Open Filename:= _ sfilename *'open the file* Set sfilename = ActiveWorkbook *'set the file name as sfilename, so the single piece of code will work with the copy-loop* b = Sheets.Count *'for the data-import loop* Call ImportData *'call in the loop* sfilename.Close *'close the file* End If
Next

end sub

2

What are you using at the moment? For each file in folder?

Possibilities include

• FileSystemObject
• Dir
• For i=1 to 12
  Monthname(i)

EDIT

Sub import_data() sPath = ThisWorkbook.Path sTemplate = "\profit for qqq.xls" For i = 1 To 12 sFileName = Replace(sTemplate, "qqq", MonthName(i)) ''Just checking If Dir(sPath & sFileName) <> "" Then Workbooks.Open Filename:= _ sPath & sFileName 'open the file* Set sFileName = ActiveWorkbook 'set the file name as sfilename, so the single 'piece of code will work with the copy-loop* b = Sheets.Count '*'for the data-import loop* ''Call ImportData '*'call in the loop* sFileName.Close '*'close the file* End If Next
End Sub

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.