SAPIEN Forums

Posted: **Fri Oct 07, 2011 4:03 am**

Hello 1)I need vbscript which search for the folder in the directory and opens the files in the folder which has .xls extension.
2) secondly from the workbook , i need to copy only two columns from
sheet1 to sheet2(based on their column header)i.e. if the column header
is "serial number" and "product id" i want to copy these two columns to
the next sheet. 3) Actually I have written a code which finds the
folder and opens the file in the folder and which is working perfect,
but I need the script for copying and pasting the columns. please help
me in doing this. If you could not follow my question please let me
know.Here is my code :

Code: Select all

   Option Explicit  On Error Resume Next     Dim oXL   Dim oFolder   Dim aFile   Dim FSO   Dim xTc      Set oXL = CreateObject("Excel.Application")   Set FSO = CreateObject("Scripting.FileSystemObject")   oXL.DefaultFilePath = "C:Documents and SettingsNarahariSrMy DocumentsRemoval Reports"     oXL.DisplayAlerts = False   msgbox (" done")   msgbox(oXL.DefaultFilePath)   if FSO.FolderExists(oXL.DefaultFilePath) then   Set oFolder = FSO.GetFolder(oXL.DefaultFilePath)   For each aFile in oFolder.Files   If Right(LCase(aFile.Name), 4) = ".xls" Then   oXL.Workbooks.Open(aFile.Name)   msgbox ("in loop")   xTc=extract(oXl)   oXL.Visible = True   End If   Next   Set oFolder = Nothing   end if   oXL.DisplayAlerts = True   oxl.workbook.close   [code]   Thanx

Posted: **Fri Oct 07, 2011 4:03 am**

Hello 1)I need vbscript which search for the folder in the directory and opens the files in the folder which has .xls extension.
2) secondly from the workbook , i need to copy only two columns from
sheet1 to sheet2(based on their column header)i.e. if the column header
is "serial number" and "product id" i want to copy these two columns to
the next sheet. 3) Actually I have written a code which finds the
folder and opens the file in the folder and which is working perfect,
but I need the script for copying and pasting the columns. please help
me in doing this. If you could not follow my question please let me
know.Here is my code :

Code: Select all

   Option Explicit  On Error Resume Next     Dim oXL   Dim oFolder   Dim aFile   Dim FSO   Dim xTc      Set oXL = CreateObject("Excel.Application")   Set FSO = CreateObject("Scripting.FileSystemObject")   oXL.DefaultFilePath = "C:Documents and SettingsNarahariSrMy DocumentsRemoval Reports"     oXL.DisplayAlerts = False   msgbox (" done")   msgbox(oXL.DefaultFilePath)   if FSO.FolderExists(oXL.DefaultFilePath) then   Set oFolder = FSO.GetFolder(oXL.DefaultFilePath)   For each aFile in oFolder.Files   If Right(LCase(aFile.Name), 4) = ".xls" Then   oXL.Workbooks.Open(aFile.Name)   msgbox ("in loop")   xTc=extract(oXl)   oXL.Visible = True   End If   Next   Set oFolder = Nothing   end if   oXL.DisplayAlerts = True   oxl.workbook.close   [code]   Thanx

Posted: **Fri Oct 07, 2011 5:19 am**

When you do any Office automation, the easiest thing to do is ti record a macro in Excel selecting the columns and converting it from VBA (Visual Basic for Applications) to vbScript. You need to probably connect to a sheets collection and do a for loop for each sheet, connect to the sheet to cell(1, 1) and Loop to the end of the row when the cell is = "" or nothing. You would look at .Cell(x, x).Value to see if it is the values you are looking for. This is where the macro would come into play, I would highlight the range and copy it like you want and see what the macro tells you.

Posted: **Fri Oct 07, 2011 5:53 am**

Thanks a lot for your response, but range may not be similar in all the workbooks(i.e worksheets). so i would like to select the column based on the column header (i.e the first row of the sheet). In my code I have already given the for loop to open each file...my goal is to write a vbscript which automatically copy/paste the columns for all the workbooks in the folder... I would appreciate if some one guide me on this.Thanx

Posted: **Fri Oct 07, 2011 6:52 am**

DO not use On Error Resume Next in your script.

Here is a starter if you don't know Excel:

Code: Select all

	
Dim xl 
Set xl = CreateObject("Excel.Application")
xl.Visible = True
Dim xlBk 
Dim sExcelFile
sExcelFile = "e:testtest.xls"
Set xlBk = xl.Workbooks.Open(sExcelFile)
Dim ws 
'Set ws = xlbk.Worksheets(1)
Set ws = xlBk.Worksheets.Add( , xlbk.Worksheets(2))
ws.Name = "Test"
	
Dim i 
For i = 2 To 10
    WScript.Echo ws.Cells(i, 1 )
    ws.Cells(i, 2 ) = "new value " & i
Next
	
i = 2
While ws.Cells(i, 1 )  ""
    WScript.Echo ws.Cells(i, 1 )
    ws.Cells(i, 3 ) = "new value " & i
    i = i + 1
Wend
	
xlBk.Save
xl.Quit

jvierra2011-10-07 13:54:01

Posted: **Fri Oct 07, 2011 8:52 am**

Run the code. It will explain itself if you step it a line at a time.

If you do not have any knowledge of VBscript then I suggest a book as the best place to start. Without fundamentals you will be lost and I don't want to write a book in a forum edit box.

SAPIEN Forums

Need help in vbscript

Need help in vbscript

Need help in vbscript

Need help in vbscript

Need help in vbscript

Need help in vbscript

Need help in vbscript