PDA

View Full Version : Solved: Locate Imported Files Using VBA??



Carpiem
04-10-2006, 08:00 PM
Hello,

Urgently need your help to solve this puzzle. I run a macro (Report), manually copy data from imported files then delete the imported files, run another macro (Compile) and finally save the workbook.

Translation:

1) “Report” Macro:
(a) Opens a folder on my hard drive. I select some files (the number of files to be opened is not restricted, but would usually be 2) and click enter.
(b) The macro converts these into excel files (.xls) and imports/inserts them into the workbook. The (imported files) sheet names are set according to the original file path.
(c) These sheets are always inserted to the left of the “Import” sheet where I have a command button that executes or starts this macro

2) I manually copy the data from the imported sheets, as at (c) above, to sheets named “First” and “Second”, which are permanent sheets, and then delete the imported sheets.

3) “Compile” Macro:
- Copies column A of “Second” sheet and pastes it into column A of “List” sheet.
- Copies column A of “First” sheet and pastes it into column A of “List” sheet.
- Selects “List” sheet and filters column A for a unique list of part numbers.
- Copies the filtered list of part numbers to a range in the “Report” sheet.

Report | Consolidate| List | First | Second |Flow4 | Flow5 | Import

The sheets order is as above, L to R.The imported sheets being “Flow4” and “Flow5”.

I can't figure out how to do the following with vba:

Find each new instance of imported/inserted sheets. Meaning I finish working with said file(s) and save the workbook. Now I start over again, but select a different set of files as at (a) above. Well my macro crashes as I am telling it to look for files that are not only named differently but also have new sheet numbers.

It seems as if I am in a Catch22 situation here.:banghead:

Just in case someone has a solution based on the order of sheets. The sheet order would be of no importance for my application.

Your suggestions are welcomed and greatly appreciated.:help

Thank you,

Carpiem

Killian
04-11-2006, 07:19 AM
Sorry, I've become terribly confused by what the problem is...

I'm assuming when you import a file a new sheet is created - and you want to keep track of which are new sheets?
If that's the case, declare a new collection then add each new sheet to it. You can then use the collection to manage what's whatDim colNewSheets As New Collection

Sub AddSheets()

Dim i As Long
Dim ws As Worksheet

For i = 1 To 5
Set ws = Sheets.Add
ws.Name = "New Sheet " & i
colNewSheets.Add ws
Next

End Sub

Sub ListNewSheets()

Dim ws As Worksheet
Dim strMessage As String

For Each ws In colNewSheets
strMessage = strMessage & ws.Name & vbLf
Next
MsgBox "Sheets added:" & vbLf & strMessage

End Sub

Carpiem
05-08-2006, 10:56 PM
Hello Killian,

I just got back from an "unplanned" trip to England.... hence the late response. Thank you for the advice.

Looking at my original question I can see it was as clear as mud. As it happens I have have reworked my project and by passed this step altogether.

However I have "Borrowed" your code for future use... :->>

Carpiem