Extract data form one Excel file to another

Guessed · #4 06-07-2023, 07:27 PM

Looks like you already have it sorted. I will make a few minor suggestions to tidy it up a bit

1. Why redim the array if you already know how many slots

Code:

Dim arrData(287) As String

2. I'm surprised the workbook opens in the same Excel application instance but it seems a little haphazard to rely on it always being the second workbook. Is it worth exploring whether it is possible to be more explicit about which workbook is oWB?

Code:

Set oWB = oILS.OLEFormat.Object  'does this work

3. You can avoid the Select Case complexity if you make use of the worksheet function to return an empty string on errored formulas

Code:

For lngRow = 1 To lngRows
    arrData(lngRow) = WorksheetFunction.IfError(Cells(lngRow, lngCol), "")
  Next lngRow

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Extract data from a Word file.	donlincolnmsof	Word VBA	3	08-31-2019 05:27 AM
Extract data from HTML File.	donlincolnmsof	Word VBA	5	08-26-2019 08:36 PM
Extract data from HTML File.	donlincolnmsof	Word VBA	0	03-07-2019 12:17 PM
Outlook 2013 Forms (how to questions)- Quick opening of a form file & Linking form's data to Excel	gamin2407	Outlook	0	01-21-2017 10:14 PM
Macro to highlight repeated words in word file and extract into excel file	aabri	Word VBA	1	06-14-2015 07:20 AM