×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Macro that accesses Excel tables

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?

RE: Macro that accesses Excel tables

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.

CODE

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

RE: Macro that accesses Excel tables

(OP)
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!

RE: Macro that accesses Excel tables

CODE

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

RE: Macro that accesses Excel tables

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources