VBA to Transpose Rows to Columns on Multiple Worksheets

Greetings All Experts,

Attached documents refer... "OriginalFile" (prior to transpose), Macro Script (code below) and "Transposed File" which contain the results after running the VBA script).

I need to transpose all values contained in Row 1 (the total columns vary from file to file - not fixed) on each worksheet into new tables named "Table1", "Table2", "Table3", etc. (the number of tables will depend on the number of worksheets, which can be anything between 3 to probably 6 worksheets). Once all worksheets has been transposed, the various Tables must be appended into 1 'Final' Table which I will then import and process into a MS Access database for further processing.

My problem is to code (populate) the section/string specifying the "C1:" to "Last Column" values...these are the values that we (internally) refer to "Product Style Codes" and are listed in the "Formula:=" section in the code below. These "Product Style Code" values will differ from file/worksheet to file/worksheet.

Please Login or Register  to view this content.

Thank you for your reply Sintek.

Will apply your code and revert back shortly.

Appreciated!

Your correction is perfect, thank you...

Sintek,

How can I incorporate your transpose code in my existing code? (I think I am using late binding since I'm calling the procedure from MS Access). As you can see from my code below, I am already looping through each worksheet to do some formatting and filtering. Would you be able to slot your code between the comments section "Start Sintek Code" and "End Sintek Code"?

I love the fact that you are not creating new 'Table' worksheets as per the "TransposedFile" example I originally sent.

Please see attached file containing my current code.

Hi Sintek,

Yeah, I'm forced to repeat the filtering due to the xlor limitation... posted another thread about it asking for an alternative solution; but was told about the filter limitation and hence I left it at that... but it's working at least :-)

Attached, please find a sample file prior to ANY modification. I have deleted quite a few columns to reduce the filesize.

THANK YOU ONCE AGAIN!

Correct, so once all of the worksheets have been transposed, I would need to import each transposed worksheet into MS Access. I thought it would be 'easier' to do by 1st appending all transposed worksheets into one worksheet and then do the import into a MS Access table.

Evening Sintek,

You have no idea how much I appreciate your efforts! i will test this 1st thing tomorrow morning and probably revert back with 10 stars (if I could). Did not expect from you to redesign all of it!! Have a great evening!! Chat tomorrow...

Apologies for not getting back to you Sintek; got distracted with other issues. Will test your code and revert back soon.

This is absolutely amazing and superfast! You are nothing short of a genius sintek!

What would be nice would be (but not a necessity) if, once the "Appended" worksheet has been created, to have the data sorted by the values in Column A ("Hdr1")and then by Column C ("Hdr3").

My intention now is to use your code as a module within my MS Access Application and call this module with an Excel File, something like "Call 33v16("D:\PreProcessed\ABC.xlsx") where "ABC.xlsx" will be the pre-formatted file.

Do you think it will work if I just add this to your code?

Public Sub 33v16(sFile as String) ...instead of just having "Sub 33v16" ?

Or will it require additional modifications? I'm thinking in terms of late- / early binding.

Kind Regards

Sintec,

So I created an MS Access module (using your code; see attached image) and tried to call the procedure with a filename: Call FormatMarkhamTest(ABC.xlsx)
Getting a compile error "Invalid or unqualified reference" on "Application.DisplayAlerts = False". Any ideas?



Your time and efforts appreciated...

Cool, actually not a bad idea... will try and revert back shortly

Works like a charm! "-" entries are all gone, and in your code

Set wb = Workbooks.Open("E:\Steven\Desktop\Tester1.xlsx")

I replaced your path above with the "sFile" value from where I call your sub.

So the MS Access Application now opens Excel and the specified file, runs your code perfectly; but I have to save and close Excel afterwards to enable my code to continue execution. My original code does the formatting without 'visually' opening Excel and the file being processed; it all happens 'in memory'. So the users does not see the formatting happening in the background.

Well well well; absolutely fantastic!!

Works 100% and exactly what I wanted Sintek.

You are a miracle worker, all kudo's to you. Do not know how to thank you enough!!

Won't be starting a new thread; problem solved... more than I originally asked for. Forever gratefull.

The .Application.Quit statement closes the MS Access application as well....

Morning Sintek,

Me bad... left out the "." before "Application.Quit"

Working now...

For some reason your code is failing on two of the files I tried to process...

On the one file the code just hangs and I cannot see the error. On the other I get a Runtime Error 1004 (Application Defined or Object Defined Error) on line ".Cells(2,1).Range(x,4).Value = Temp"

Unfortunately it seems that I am unable to attached the 2 .zip files (Get a message that the website is unavailable).

The one file is 3.9MB and the other is 4.2MB ...is there an alternative way that I can send these two files to you to investigate?

Regards

Hi,

Thank you, will test quickly...

If no quantities in a worksheet it can just be ignored...final result should only include rows where quantities/values > 0 (or <> "-"

Will also check WK 9 again.

Hi,

Changing the Evaluate function unfortunately did not resolve the mentioned issue, nor the Wait time > 4 seconds. But it's working for all the other files :-)

Not to worry about it... we can work around this!

Thank you once again, immensely appreciated.

I'm not going to bother any further...tried stepping through; but don't have the time; takes forever :-)

If we do encounter an error I'll let you know...all good for now!

Thank you very very much.

Morning Sintek,

Trust you are well?

I have been running into a problem when trying to import the 'reformatted' file output that your code generate into Access. I know that by removing all worksheets except for the worksheet named "Appended" will resolve my problem.

To put it another way, I am only interested in processing the data contained in the "Appended" worksheet.

May I ask you to add the relevant code to accomplish this to the code you provided?

Many Thanks...your code below:

Please Login or Register  to view this content.

Hi "sintek",

Thank you, just added the code below before the wb.Save and wb.Close statements and it worked...

Please Login or Register  to view this content.

Apologies!!!

Only saw your filepath highlighted in red and assumed your copy statement "I know that by removing all worksheets except for the worksheet named "Appended" will resolve my problem." implied that I should try and resolve it myself... :-) Did not realize you removed some of your original code.

Your shortened code works, as expected, 100% and is a much better solution.

Apologies once again for misinterpreting your reply...and thank you, thank you!! Appreciated!

I am now able to process the final product into my MS Access application successfully :-)

Afternoon Sintek,

Trust you are well?

There's something funny going on when executing your code on more than one Excel file... If I run it a 1st time on say FileNameNo1 it runs perfectly. If I run it again straight afterwards with either the same FileNameNo1 (or any other file for that matter, the code crashes with Run time Error '462' ("The remote server machine does not exist or is unavailable" and it seems to happen on the line:

With wb.Sheets.Add(,Sheets(Sheets.Count)): .Name = "Appended": End With

What I do find strange is that if I close my application after the 1st (always successfull) run and re-open the application again and do a 2nd file, then the 2nd file will process without any errors. So it seems by closing the application after each processed file eliminates any errors. It's almost as if by closing the application; certain parameters or variables are being resetted (which doesn't happen if I try to process a 2nd file after the 1st successful run. Some assigned value remain in memory somewhere which causes the program to crash when processing consecutive files.

Any idea's??

Morning Sintek,

I think I may have stumbled across an error that may shed some light on the fact that the code fails on subsequent files. When processing a 2nd file I received an error stating that the ".Name" value 'Appended' is already in use" on the line below. As mentioned before, the 1st file after program startup always process 100%; but code fails on subsequent files. If I close my program and reopen after each processed file the code works 100%...it's something to do with the "Appended" value not being released (from memory) or reset properly after processing the 1st file.

PS : I have added these two line at the bottom hoping it would help; but it does not seem to make any difference.

Please Login or Register  to view this content.

If you can't think of anything I will submit on an Access forum with your consent to include your code?... don't want to waste your time any further. Just thought it might be a quick fix for you...

Please Login or Register  to view this content.

Current Code below:

Public Sub FormatMarkham01(sFile As String)

Please Login or Register  to view this content.

Hi,

The sFile variable is a combination of 2x string variables) and FormatMarkham01 gets called from another Module with a path to the file that's to be formatted:

Please Login or Register  to view this content.

None of the original files that we receive from our customer that we select for processing contain a worksheet named "Appended". The "Appended" worksheet only gets added by your code.

So here's the two scenario's, Scenario 1 will result in errors when processing a 2nd file, whilst Scenario 2 will process the 2nd file successfully.

Scenario 1:
Steps:
Open Access Application
Select File "A" to Process
Result = Success (No Error on 1st file)
Select File "B" to Process
Result = Fail - Error on line

Please Login or Register  to view this content.

(Should I at this stage close Access, reopen Access and again select File B, the selected file will process successfully (after the close/open of Access))

Scenario 2:
Steps:
Open Access Application
Select File "A" to Process
Result = Success (No Error)
Close Access Application
Open Access Application
Select File "B" to Process
Result = Success (No Error)
Close Access Application
Open Access Application
Select File 'C" to Process
Results = Success (No Error)
.etc


Hope that makes more sense :-) ?

Will try #50 quickly and revert back :-)

You are a freaking genius!!!!

No errors whatsoever :-) :-) :-) !!!

Here's the final working code:

Please Login or Register  to view this content.

I cannot thank you enough!!

Noted; it was part of my troubleshooting...

Originally Posted by sintek

Excel should clear the object when it goes out of scope...

Please Login or Register  to view this content.

Hi, but often Excel forget to clean object variables until it is finally closed,
the reason why the better is the VBA procedure must release object variables in order to avoid some classic memory Excel crash …

Hi Marc L,

Thank you for clearing that up... I will therefor leave the two statements in the module, just to be safe.

In common use there is no issue but if Excel stays opened during the workday and
the same VBA procedure is launched many times without releasing object variables then a crash may arise …

In fact as Excel needs to load everything in memory and as in classic Excel 32 bits version the memory it can managed is limited
then the more unreleased objects the more chance to crash, like I met the case in a company …

Thank you both for your comments; well noted.