Posts by chand123

chand123 · Aug 31st 2016

Re: loop code through all open workbooks

Hi cytop,

yes you are right but Personal workbook is the macrobook and it run in the background so by "loop code through all open workbooks" i mean workbook that i open and not the personal workbook and i am really very sorry if I have not provided the relevant information.

chand123 · Aug 31st 2016

Re: loop code through all open workbooks

Hi cytop,

thanx for your reply but the problem is that as I am placing & running this code from my personal workbook the above code run on personal workbook also and saves all the files to personal workbook path.

chand123 · Aug 30th 2016

Re: loop code through all open workbooks

Hi cytop,

I have made some changes to the code but it does not Saveas the last workbook and keeps it open.

Can you guide me.

Code

Sub SAVEasXLSB()


   Application.ScreenUpdating = False
   
   Dim wbkX As Workbook
   Dim fpath As String
   Dim wname As String
   
   
  For Each wbkX In Application.Workbooks
   
   
'wname = ActiveWorkbook.Name
Set wbkX = ActiveWorkbook


With wbkX


wname = Left(wbkX.Name, (InStrRev(wbkX.Name, ".", -1, vbTextCompare) - 1))
fpath = wbkX.path & "\" & wname




.SAVEas filename:=fpath & ".xlsb", FileFormat:= _
        xlExcel12, CreateBackup:=False
        
        wbkX.Close
        


   End With


Next wbkX






 Application.ScreenUpdating = True


End Sub

Display More

chand123 · Aug 30th 2016

Re: loop code through all open workbooks

Hi cytop,

Good Morning, Sorry for the late reply yes i will edit the code as you said and reply.

chand123 · Aug 29th 2016

Hi,

I want code to run on all open workbooks following is the code I wrote but it does work properly

any help will be appreciated.

Code

Sub SAVEasXLSB()


   Application.ScreenUpdating = False
   
   Dim wbkX As Workbook
   Dim fpath As String
   Dim wname As String
  
  Set wbkX = ActiveWorkbook
   
   
   
'wname = ActiveWorkbook.Name
wname = Left(ActiveWorkbook.Name, (InStrRev(ActiveWorkbook.Name, ".", -1, vbTextCompare) - 1))
fpath = ActiveWorkbook.path & "\" & wname






For Each wbkX In Application.Workbooks
'With wbkX


ActiveWorkbook.SAVEas filename:=fpath & ".xlsb", FileFormat:= _
        xlExcel12, CreateBackup:=False
        
        ActiveWorkbook.Close
        


   'End With


Next wbkX




 Application.ScreenUpdating = True


End Sub

Display More

i have attached the sample files for your reference.

Thanks in advance

chand123 · Aug 26th 2016

Re: Activeworkbook saveas xlsb in same location

Hi cytop,

Thanks a ton now it is perfect.:thumbcoo:

chand123 · Aug 26th 2016

Re: Activeworkbook saveas xlsb in same location

Hi cytop,

Code

Sub SAVEasXLSB()


   Application.ScreenUpdating = False
   
   Dim fpath As String
   Dim wname As String
   
wname = ActiveWorkbook.Name
fpath = ActiveWorkbook.path & "\" & wname


ActiveWorkbook.SAVEas filename:=fpath & ".xlsb", FileFormat:= _
        xlExcel12, CreateBackup:=False


 Application.ScreenUpdating = True


End Sub

Display More

I wrote this code and it works for me as i want but the only problem is if I open a file name 123.xlsx and run the code it saves it as 123.xlsx.xlsb is there any thing that i need to change to save it as 123.xlsb.

Thanks.

chand123 · Aug 26th 2016

Re: Activeworkbook saveas xlsb in same location

Hi cytop,

thanks for you reply but i am Sorry, i did not got you, below is code that i recorded

Code

ActiveWorkbook.SaveAs Filename:= _
            "C:\Users\chandrakant\Desktop\Output\123.xlsb", FileFormat:= _
        xlExcel12, CreateBackup:=False

here the path is constant and i don't want the path to be constant.

chand123 · Aug 26th 2016

Re: Activeworkbook saveas xlsb in same location

hi cytop,

Yes i tried to record a macro but it records the path and the path becomes constant but i don't want the path to be constant because if i open a file from some other location and want to saveas "xlsb" it will save in the recorded location and not in the active file location .

I hope this makes you understand what I want to say.

chand123 · Aug 26th 2016

hi,

Need a VBA to Save Active workbook as xlsb in same i.e active workbook name and location.

Thanks in advance.

chand123 · Apr 18th 2016

Re: Inventory Ageing

Dear MrRedli,

Thankyou for reply but this formula does not help because for the second invoice for the same item it gives "0" in column SOH in Invoice sheet. for example if we change the qty for "KC 220" from to 3 to 6 in SOH sheet it should take 5 from first invoice and 1 from second invoice for the same Item.

chand123 · Apr 15th 2016

Re: Inventory Ageing

Dear MrRedli,

Thank you, but this formula does not help because when the item SOH qty is More than the first invoice qty it does not jump to another invoice for the same item instead it gives gives 0 in SOH column i.e. when you change the qty of "KC 220" from 3 to 6 in Stock Sheet the formula gives 6 value in invoice sheet and what i want is the formula should take 5 from first invoice and 1 from next invoice.
[TABLE="width: 141"]

[tr]

[TD="class: xl63, width: 141"][/TD]

[/tr]

[/TABLE]

chand123 · Apr 13th 2016

Re: Inventory Ageing

Sorry, Please find the attached file for your reference.

chand123 · Apr 13th 2016

Re: Inventory Ageing

Dear MrRedli,

Please see the attached file for your reference.

chand123 · Apr 12th 2016

Re: Inventory Ageing

Quote from MrRedli;768946

hi Chand, why don't you just add a vlookup as only thing missing from invoice file is SOH

Dear MrRedli,

Thank you for your reply but with VLOOKUP I cannot achieve the desired output because as you can see in the file you attached when the item goes in 2 invoices the VLOOKUP gives the same value for all the duplicates or repeats and my problem is in my files the duplicates can be around 10 to 15 thousand so i cannot check and make changes manually.

chand123 · Apr 11th 2016

Re: Inventory Ageing

Dear FDibbins,

Thanks for the reply, I have attached the samples files for your reference which are not so bulky you can see the size of "Sample Files.rar" attached which is only 37.1 kb. I need the code urgent.

chand123 · Apr 9th 2016

Re: Inventory Ageing

Sorry For the Mistake

chand123 · Apr 8th 2016

Re: Inventory Ageing

Quote from Grimes0332;768796

Double post http://www.ozgrid.com/forum/showthread.php?t=199341

Yes I know but i got no response for my post so i posted it again

chand123 · Apr 8th 2016

Hi,

I want to calculate the age of the product lying at the store, I scratched my head a lot but was not able to do so.

I have a Invoice data in One Excel and Stock on Hand (SOH) data in another excel, I want Invoice no and Invoice date against the Customer name, Item code and Item Description.

My problem is I have huge data around 3 to 5 lakh rows in both files so cannot do it manually. So I need a VBA Code to perform the task and do it faster.

I have attached the sample file for your reference. Please check the data marked in red in Output file it is marked red because the Product i.e. Item has gone in two invoices, it should take the latest invoice first.

Any Help will be highly appreciated.

Thanks in Advance.

chand123 · Apr 7th 2016

Re: Inventory Ageing

Please Help.......... its urgent