Macro that accesses Excel tables

[JIM74]

How do I access the data from an Excel table using a VB Script macro?

 

[handleman]

Here are my assumptions:

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.

dim xlApp		'Excel Application Object
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

 

[JIM74]

What I'm exactly trying to do is use a VBScript macro to reference an excel worksheet column of values. This column contains part numbers that I need the macro to process a function for 1 at a time all the way down the column. I appreciate your help by the way. Can you help me try and do this? Thanks in advance!

 

[handleman]

dim xlApp		'Excel Application Object
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

 

[JIM74]

Thanks alot! It worked like a charm! I really appreciate your help on this!