Posts by ranjith_12

ranjith_12 · Jun 21st 2020

Application.ScreenUpdating = False

Dim FileToOpen As Variant
Dim OpenBook As Workbook, shAllBranch As Worksheet
Dim r As Long

r = Range("A6").End(xlDown).Row + 1

FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", Filefilter:="Excel Files (*.xls*),*xls*")
' FileToOpen = Application.GetOpenFilename(Filefilter:="Excel Files (*.xlsx), *.xlsx", Title:="Select Workbook to Import", MultiSelect:=True)

If FileToOpen <> False Then
Set ActiveSheet = Application.Workbooks.Open(FileToOpen)
ActiveSheet.Sheets(1).Range("A1:L1").Copy
ActiveWorkbook.Worksheets("Sheet1").Range("A" & r).PasteSpecial xlPasteValues
End If
Application.ScreenUpdating = True

End Sub

Afterwards need to short based on coulmn A in the master sheet

ranjith_12 · Jun 20th 2020

Sub Import_BOM_DATA()

Application.ScreenUpdating = False

Dim FileToOpen As Variant
Dim OpenBook As Workbook, shAllBranch As Worksheet
Dim r As Long

r = Range("A6").End(xlDown).Row + 1

FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", Filefilter:="Excel Files (*.xls*),*xls*")
' FileToOpen = Application.GetOpenFilename(Filefilter:="Excel Files (*.xlsx), *.xlsx", Title:="Select Workbook to Import", MultiSelect:=True)

If FileToOpen <> False Then
Set ActiveSheet = Application.Workbooks.Open(FileToOpen)
ActiveSheet.Sheets(1).Range("A1:L1").Copy
ActiveWorkbook.Worksheets("Sheet1").Range("A" & r).PasteSpecial xlPasteValues
End If
Application.ScreenUpdating = True

End Sub

Afterwards need to short based on coulmn A

ranjith_12 · Apr 1st 2020

For your remainder. please help on this

ranjith_12 · Mar 23rd 2020

Get you get change to review attachment Code ?

ranjith_12 · Mar 17th 2020

Attached Code file for your help. Looking VB is projected condition. please try with some pwd.

ranjith_12 · Mar 16th 2020

Did you get change look at the code ?

ranjith_12 · Mar 14th 2020

if i directly mention conversion number . Getting " Kill sPath as error".

Code

ActiveWorkbook.SaveAs Filename:=targetfile & ".xls", FileFormat:=xlExcel8
End If
Set wb = ThisWorkbook
sPath = ThisWorkbook.Path
NewName = Left(wb.Name, Len(wb.Name) - 5)
Application.DisplayAlerts = False
ThisWorkbook.SaveAs sPath & Application.PathSeparator & NewName, 51 '///".xlsx", FileFormat:=51 ''///51 = xlsx
ActiveWorkbook.SaveAs sPath & Application.PathSeparator & NewName, 56 '///".xls", FileFormat:=56 ''///56 = xls
Application.DisplayAlerts = True
''/// optional
''/// delete the xlsx file
sPath = sPath & Application.PathSeparator & NewName & ".xlsx"
Kill sPath

Display More

ranjith_12 · Mar 14th 2020

if i run your code . Getting error argument already specified (Highlighted 51 red)

Code

FileFormat:=51

Code

ActiveWorkbook.SaveAs Filename:=targetfile & ".xls", FileFormat:=xlExcel8
End If
Set wb = ThisWorkbook
sPath = ThisWorkbook.Path
NewName = Left(wb.Name, Len(wb.Name) - 5)
Application.DisplayAlerts = False
ThisWorkbook.SaveAs sPath & Application.PathSeparator & NewName, ".xlsx", FileFormat:=51 ''///51 = xlsx
ActiveWorkbook.SaveAs sPath & Application.PathSeparator & NewName, ".xls", FileFormat:=56 ''///56 = xls
Application.DisplayAlerts = True
''/// optional
''/// delete the xlsx file
sPath = sPath & Application.PathSeparator & NewName & ".xlsx"
Kill sPath
ActiveWorkbook.Close
End Sub

Display More

ranjith_12 · Mar 14th 2020

Remaining thinks works fine. i am getting msg box "Can't perform operation since the project is protected." . If i give End then it looks good. How to ignore this msg using code ?

ranjith_12 · Mar 11th 2020

Same way i tried . please find latest code saved

ranjith_12 · Mar 11th 2020

My badness output file extension is not recognized, So getting Error and also output file have module code

Code

targetfile = Range(cell_target_File).Value + " Enovia Upload"
ThisWorkbook.SaveAs Filename:=targetfile
Call SaveToXLS
Module2
Sub SaveToXLS()
Dim wb As Workbook
Dim sPath As String, NewName As String
Set wb = ThisWorkbook
sPath = ThisWorkbook.path
NewName = Left(wb.Name, Len(wb.Name) - 5)
Application.DisplayAlerts = False
ThisWorkbook.SaveAs sPath & Application.PathSeparator & NewName, 51''///51 = xlsx
ActiveWorkbook.SaveAs sPath & Application.PathSeparator & NewName, 56 ''///56 = xls
Application.DisplayAlerts = True
''/// optional
''/// delete the xlsx file
sPath = sPath & Application.PathSeparator & NewName & ".xlsx"
Kill sPath
ActiveWorkbook.Close
End Sub

Display More

ranjith_12 · Mar 11th 2020

Getting Compile error: Named argument already specified and multiple warning error

Code

targetfile = Range(cell_target_File).Value + "Upload"
ThisWorkbook.SaveAs Filename:=targetfile, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.SaveAs ActiveWorkbook.Name, (".xls"), FileFormat:=56

ranjith_12 · Mar 11th 2020

Output file using it for different application, that application will allow only Xls format. is it possible to save it in Xlsx and then overwrite it to Xls within master code template?

ranjith_12 · Mar 11th 2020

i have master macro template (.xlsm) help to transferring data from "A file to master template. Input "A" maybe allow two different format ( .Xlsx Or .xls) , then saveas file output file (B) should be files as (.xls) . Master template i protected pwd code in VB properties. Like to remove all code in Output file (B). How can i unprotected VB project using code to delete Module codes ? . Getting Error if master file is protected not allowing me to run removing codes. If i unprotected master template by manually , it allows to delete code in output file.

Code

Workbooks(originalworkbook).Close SaveChanges:=False
targetfile = Range(cell_target_File).Value + " BOM Upload"
If targetworkbook = (".xlsx") Then
ActiveWorkbook.SaveAs Filename:=targetfile & ".xls", FileFormat:=xlExcel8
Application.DisplayAlerts = False
Else
ActiveWorkbook.SaveAs Filename:=targetfile & ".xls", FileFormat:=xlExcel8
End If
Application.ScreenUpdating = True
' Remove macro code from saveas file
Set comps = ActiveWorkbook.VBProject.VBComponents
For Each comp In comps
Select Case comp.Type
Case vbext_ct_StdModule, _
vbext_ct_MSForm, _
vbext_ct_ClassModule
comps.Remove comp
Case Else ' Worksheet, Workbook, etc.
comp.CodeModule.DeleteLines 1, _
comp.CodeModule.CountOfLines
End Select
Next comp
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

Display More

ranjith_12 · Mar 10th 2020

i am trying to delete Module1 and Thisworkbook macro in saveas file.but macro is not deleting the saveas file

Code

Workbooks(originalworkbook).Close SaveChanges:=False
targetfile = Range(cell_target_File).Value + " Upload"
If Not targetworkbook = (".xlsx") Then
ActiveWorkbook.SaveAs Filename:=targetfile & ".xls", FileFormat:=xlExcel8
Application.DisplayAlerts = False
Else
ActiveWorkbook.SaveAs Filename:=targetfile
Application.DisplayAlerts = False
End If
Set Workbooks(".xls").Worksheets(" Upload Template").Activate
ActiveWorkbook.Unprotect ("mypwd")
Set vbCom = Application.VBE.ActiveVBProject.VBComponents
vbCom.Remove VBComponent:= _
vbCom.Item("Module1")
Application.EnableEvents = False
'Remove all code from ThisWorkbook code module
ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule.DeleteLines 1, _
ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule.CountOfLines
Application.EnableEvents = False
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

Display More

Posts by ranjith_12

Import data from get file to master file sheet (Macro sheet)

Import data from get file to master file sheet (Macro sheet)

Your help is need to fix program input

Your help is need to fix program input

Your help is need to fix program input

Your help is need to fix program input

Your help is need to fix program input

Your help is need to fix program input

Your help is need to fix program input

Your help is need to fix program input

Your help is need to fix program input

Your help is need to fix program input

Your help is need to fix program input

Your help is need to fix program input

Your help is need to fix program input