Thread: Using VB to Access Excel Data

I need to write a VB program that will open a spreadsheet, get data from one or more worksheets and then put the data into a database for additional processing and report generation. I have written programs to get data out of spreadheets before and put data into a database so I am not worried about how to do that. The challenge is to find the data in the one or more worksheets in a workbook.

I am working with estimators that use spreadsheets to estimate jobs. They have a template worksheet that they use. Within this worksheet is a range of cells that have all of the data that I need. The problem is that the summary data is never in the same location. The more complex the job, the more rows in the spreadsheet which moves the summary data further down. To further complicate things, a simple job may have one worksheet while complicated ones will have several. I need to find the summary data in each worksheet within the workbook.

I thought that I could name the range and would be able to access the name in each worksheet but I found that when I tried to define a range in a worksheet with the same name as another worksheet, it simply updated the range.

I need to make this as simple as possible. Is there a way to define a range in a worksheet that I could find in multiple worksheets within a workbook?

How do I get a list of worksheets within a workbook?

How do I find the range of cells that I am looking for within each worksheet? The range will always be the same size but it will be in a different location within each worksheet.

Any help or tips in the right direction will be appreciated.

Thanks

John

Hi

The following code will copy each named range in a specified (source) workbook into the master workbook. It's basic, but it's a start...

I've attached the 2 files fyi.

Dion

Sub ImportRanges()

Dim wbSource As Object
Dim varNameRange As Name
Dim wsTarget As Integer
Dim varTrgSheetName As String

Workbooks.Open "C:\Documents and Settings\Source File.xlsx"

Set wbSource = ActiveWorkbook
wsTarget = 1

For Each varNameRange In wbSource.Names
    varTrgSheetName = Mid$(varNameRange, 2, 6)
    Sheets(varTrgSheetName).Select

    Range(varNameRange).Select
    Selection.Copy

    ThisWorkbook.Activate
    Sheets(wsTarget).Select
    Range("A1").Select
    ActiveSheet.Paste
    
    wsTarget = wsTarget + 1
    wbSource.Activate
Next varNameRange

wbSource.Close False

Set wbSource = Nothing

End Sub

I can't get this code to work at all. I added the Excel interop reference but it doesn't help very much. Just looking at the code shows a bunch of variables that don't have any reference.

Sorry, but I don't see how this code works.