• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Split records into Excel Files Based on Value from a Column-VBA

R

rkbisht2019

Member
Dear All,

In my attached excel file, when I run macro to split records zone wise into individual excel files, everything goes perfect until loop executes records of Zone- "NORTH 2 (UP/UK)".

Please run macro on my excel file and help me in fixing this issue.

Although, alternatively if I rename zone- "NORTH 2 (UP/UK)" with "North-2" error is eliminated. But it leads to overhead of one more task to again renaming zone name, once records is exported.
 

Attachments

  • PK_Dunning.xlsm
    728 KB · Views: 7
Last edited:
Hi,​
​
first when you create a thread for a VBA question pay attention to be in the appropriate VBA forum section rather than the Excel formulas one !​
​
Your issue is the same as in your previous thread : filenames forbidden characters within the column A and in your code as well !​
So obviously just remove / replace them or well elaborate at least with details what is your strategy …​
​
 
Marc L said:
Hi,​
​
first when you create a thread for a VBA question pay attention to be in the appropriate VBA forum section rather than the Excel formulas one !​
​
Your issue is the same as in your previous thread : filenames forbidden characters within the column A and in your code as well !​
So obviously just remove / replace them or well elaborate at least with details what is your strategy …​
​
Click to expand...
Dear Marc, it is standard zone name which is decided by management. I have to report each zone's record daily. Thus, to automate splitting records zone wise. I have sought VBA macro. Although, this code is working but I have to rename zone name twice times. If you can provide working VBA code then I will really appreciate it. I have gone pillar to post for solution, in every code same issue found.

I believe there is nothing impossible in technical.
 
Last edited:
So you do not ever need to rename the source data in column A but just do not use any forbidden character in the filename.​
To provide any code as I can't guess anything you must explain at least what is your strategy !​
Just sharing a sample of a 'bad' cell in column A and which filename you would use for it …​
​
 
rkbisht2019
About Your it is standard zone name which is decided by management.
Do that mean ... Your management can overrule common naming rules?
There could be possible use those names too, but not with Windows.
Do Your management mean that too?
Could Your management make new decision to use valid filename characters?
 
vletm said:
rkbisht2019
About Your it is standard zone name which is decided by management.
Do that mean ... Your management can overrule common naming rules?
There could be possible use those names too, but not with Windows.
Do Your management mean that too?
Could Your management make new decision to use valid filename characters?
Click to expand...
Dear,
In simple terms, there is predefined format of data with zone name available at frontend, which is not compatible for filtering records using VBA I believe, but when I manually filter records there is no issue found. You may try the same. I am stunned why it becomes bad name against windows rule? If there is no solution then VBA is not good choice.
 
Marc L said:
So you do not ever need to rename the source data in column A but just do not use any forbidden character in the filename.​
To provide any code as I can't guess anything you must explain at least what is your strategy !​
Just sharing a sample of a 'bad' cell in column A and which filename you would use for it …​
​
Click to expand...
Dear Marc,
If you open my excel file, you will find data with zone. Now if you filter every records by zone name and copy-paste filtered data into Excel file there will not be any issue.

My concern is that if I execute same task through macro driven approach why "Forbidden Character" logic occurs? There must be alternative approach fixing this issue. Please atleast open my excel file and see if you can fix this issue.
 
rkbisht2019
I asked three basic questions about file naming -- did You answer any of those?
You used term 'simple' ... that means many times 'do double work'.
 
rkbisht2019 said:
which is not compatible for filtering records using VBA I
Click to expand...
As there is no issue to filter under VBA, just watching on which codeline the error raises in your code … :rolleyes:​
​
rkbisht2019 said:
There must be alternative approach fixing this issue
Click to expand...
Yes as yet explained in posts #3, #5 & #6 and in post #17 of your previous thread as well !​
Again the issue does not come from VBA but from Windows side !​
To understand a test at kid level : open the Windows Explorer and try to rename any file as NORTH 2 (UP/UK)​
then see what happens. For good enough readers only …​
If you still do not understand, see with your IT.​
​
rkbisht2019 said:
see if you can fix this issue.
Click to expand...
Yes once you understand the reason why this issue comes - so easy just achieving the test above ! -​
and once you answer to my question, post #5 to read again but well this time …​
​
 
rkbisht2019
If You really would like to use those Settings-sheets zones then
You should have there NORTH 2 (UP and NORTH 3 (HR -folders. Then it possible save almost as You want. Except file names would be UK and PB in those folders.
OR
Use eg row number for file name or any other text, which has valid characters.
 
I don't know why suggestions are floating on this thread? Rather noone is giving working code for this issue. Everything is in place to test, if someone is good at MACRO then simply that person needs to download my excel file and run macro, all the records zone wise will get into place defined in PATH H6. It is my humble request, if someone can't give solution then please don't waste his/her time here in comments
 
According to the easy Windows test described in post #10 and from its so obvious message you should already have read​
so what is your choice for the forbidden character in the file name ?​
As you can also ask your IT to make your strategy …​
​
 
You must log in or register to reply here.
Back
Top