Macro that accesses Excel tables
Macro that accesses Excel tables
(OP)
How do I access the data from an Excel table using a VB Script macro?
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS Come Join Us!Are you an
Engineering professional? Join Eng-Tips Forums!
*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail. Posting GuidelinesJobs |
Macro that accesses Excel tables
|
RE: Macro that accesses Excel tables
1. You have Microsoft Excel installed on the system on which you want to run the script.
2. You want to run this as a separate VBScript file.
3. By "Excel table" you mean the cells in the worksheet.
Copy the code below into a new text file. Change the extension from ".txt" to ".vbs"
Look in the help in the VBA editor in Excel for how to manipulate Excel further through the script.
CODE
dim xlWorkbook 'Excel Workbook object
dim sWkBkPath 'String
set xlApp = CreateObject("Excel.Application")
sWkBkPath = InputBox("Enter the path to the desired workbook")
set xlWorkbook = xlApp.Workbooks.Open(sWkBkPath)
xlApp.Visible = True 'Un-Comment this line if you want to see the workbook
'**************************
'Here is where you can access the data in the
'excel file with lines like this, which returns
'the value of the text in cell "C5" (Row 5, Column 3)
MsgBox xlApp.ActiveSheet.Cells(5,3).Text
'**************************
'xlWorkbook.Save 'Do this if you intend to make and save changes
xlWorkbook.Close
xlApp.Quit
set xlWorkbook = Nothing
set xlApp = Nothing
RE: Macro that accesses Excel tables
RE: Macro that accesses Excel tables
CODE
dim xlWorkbook 'Excel Workbook object
dim sWkBkPath 'String
dim nRow
dim nCol
dim sPartNumber
set xlApp = CreateObject("Excel.Application")
sWkBkPath = InputBox("Enter the path to the desired workbook")
set xlWorkbook = xlApp.Workbooks.Open(sWkBkPath)
'xlApp.Visible = True 'Un-Comment this line if you want to see the workbook
'**************************
'Here is where you can access the data in the
'excel file
nCol = 3 'Change this to whatever column you're using
nRow = 1 'Change this to the first row you want to process
sPartNumber = xlApp.ActiveSheet.Cells(nRow, nCol).Text
'While loop will exit when a blank cell is reached. This assumes that
'your part number list is continuous with no blanks
While sPartNumber <> ""
'Do your processing here
nRow = nRow + 1
sPartNumber = xlApp.ActiveSheet.Cells(nRow, nCol).Text
Wend
'**************************
'xlWorkbook.Save 'Do this if you intend to make and save changes
xlWorkbook.Close
xlApp.Quit
set xlWorkbook = Nothing
set xlApp = Nothing
RE: Macro that accesses Excel tables