Need help in vbscript

Anything VBScript-related, including Windows Script Host, WMI, ADSI, and more.
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
This topic is 12 years and 5 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
User avatar
rashme15
Posts: 5
Last visit: Wed Oct 12, 2011 5:47 am

Need help in vbscript

Post by rashme15 »

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
User avatar
rashme15
Posts: 5
Last visit: Wed Oct 12, 2011 5:47 am

Need help in vbscript

Post by rashme15 »

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
User avatar
rasimmer
Posts: 182
Last visit: Fri Apr 25, 2014 7:00 am

Need help in vbscript

Post by rasimmer »

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.
User avatar
rashme15
Posts: 5
Last visit: Wed Oct 12, 2011 5:47 am

Need help in vbscript

Post by rashme15 »

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
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Need help in vbscript

Post by jvierra »

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
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Need help in vbscript

Post by jvierra »

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.

This topic is 12 years and 5 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked