Saving files based on lookup

[André]

Hi,

I have many many files which I want to save in different folders. All files
has a number in C1 and based on that number, I need to do a lookup in a
separate file containing the name of the folder. The file has to saved in
the corresponding folder.

Ex:
File A has number 123 in cell C1
Folder-file looks like this:
Number Foldername
123 Folder A
234 Folder B
etc.
In this example, Folder A is the correct folder, and the file needs to be
moved from the current location to the subfolder "Folder A".
Repeat for all files in top-folder.

I guess this is quite simple to program in VBA ..... if you know how.....

Any help would be most appreciated.

Regards,
André

 

[DanDungan]

Hi Andre,

I found this in the archive. Maybe it will help.

Hi,
I need to save 1 workbook into different folders based on cell.value
The folders are already created in a directory with a customer number
(5
digits).

On the workbook, the customer number is saved in E:1. If more than one
customer, the values are comma separated.

Based on these values, the macro should find the folder for each
customer
and save the file on that folder adding today's date to the sheet
name.
Last, delete the original file (It is saved in a temp folder)

any ideas I will greatly appreciate. Thanks
Gaba


2. Chip Pearson
Newsgroups: microsoft.public.excel.programming
From: Chip Pearson <[email protected]>
Date: Wed, 25 Feb 2009 17:10:25 -0600
Local: Wed, Feb 25 2009 4:10 pm
Subject: Re: Save 1 file to different folders based on cell.value

Try code like the following:

Sub AAA()
Dim V As Variant
Dim N As Long
Dim FileName As String
V = Split(Range("E1"), ",")

For N = LBound(V) To UBound(V)
FileName = "C:\" & CStr(V(N)) & "\" & ThisWorkbook.Name
On Error Resume Next
Kill FileName
On Error GoTo 0
ThisWorkbook.SaveCopyAs FileName
Next N
ThisWorkbook.ChangeFileAccess xlReadOnly
Kill ThisWorkbook.FullName
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

3. Gaba
View profile
Thanks Chip for your quick answer. I'm getting an error 400 (?) it is
saving only the first value when I have more than one (comma
separated). If I have only one value, no error, no problem. Any ideas?
Thanks in advance, Gaba -

 

[André]

Thank you for your reply

I still need to find the folder name in a seperate list, based on a number
in each file. Any suggestions on how to do this?


André


"DanDungan" skrev i nyhetsmeldingen:
(e-mail address removed) ...

Hi Andre,

I found this in the archive. Maybe it will help.

Hi,
I need to save 1 workbook into different folders based on cell.value
The folders are already created in a directory with a customer number
(5
digits).

On the workbook, the customer number is saved in E:1. If more than one
customer, the values are comma separated.

Based on these values, the macro should find the folder for each
customer
and save the file on that folder adding today's date to the sheet
name.
Last, delete the original file (It is saved in a temp folder)

any ideas I will greatly appreciate. Thanks
Gaba


2. Chip Pearson
Newsgroups: microsoft.public.excel.programming
From: Chip Pearson <[email protected]>
Date: Wed, 25 Feb 2009 17:10:25 -0600
Local: Wed, Feb 25 2009 4:10 pm
Subject: Re: Save 1 file to different folders based on cell.value

Try code like the following:

Sub AAA()
Dim V As Variant
Dim N As Long
Dim FileName As String
V = Split(Range("E1"), ",")

For N = LBound(V) To UBound(V)
FileName = "C:\" & CStr(V(N)) & "\" & ThisWorkbook.Name
On Error Resume Next
Kill FileName
On Error GoTo 0
ThisWorkbook.SaveCopyAs FileName
Next N
ThisWorkbook.ChangeFileAccess xlReadOnly
Kill ThisWorkbook.FullName
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

3. Gaba
View profile
Thanks Chip for your quick answer. I'm getting an error 400 (?) it is
saving only the first value when I have more than one (comma
separated). If I have only one value, no error, no problem. Any ideas?
Thanks in advance, Gaba -