How do I define a range object when I'm not in Excel?-CodePudding

I'm working on a VBA script in MS Project, which involves the creation of a workbook. But I'm doing something wrong when defining a range, and it seems like it works differently because I'm not in Excel.

This is my current code, boiled down to the problematic part:

Dim myExcel As Object
Dim myWb As Object
Dim myRange as Object 'Not sure if I should declare this one as an object or a range
Dim myRangeString as String

Set myExcel = CreateObject("Excel.Application")
Set myWb = myExcel.Workbooks.Add

myRangeString = "$D$20, $C$23" 'For simplicity. Obviously this is not hard coded.

Set myRange = Range(myRangeString)

This code works fine in Excel. But in Project it stops and throws back "Run-time error '1004': Method 'Range' of object '_Global' failed.". Maybe that's because "Global" in this context is Project and not Excel. So I have tried different variations of MyExcel.Range(myRangeString), but then I get "Application-defined or object-defined error." instead.

Can anyone help?

CodePudding user response：

Just like @BigBen and @Darren, I don't have MS Project either, but I have a similar crossed-application VBA Script among Excel and Outlook and putting the "app" before worked quite well for me. Here's the snippet:

Public Sub XPTO()

Dim xExcelFile As String
Dim xExcelApp As Excel.Application
Dim xWb As Excel.Workbook
Dim xWs As Excel.Worksheet
Dim xExcelRange As Excel.Range
   
'Get the dimension table
xExcelFile = "C:\path\toMyFile\File.xlsm"
Set xExcelApp = CreateObject("Excel.Application")
Set xWb = xExcelApp.Workbooks.Open(xExcelFile)
Set WKB = xExcelApp.ActiveWorkbook
Set xExcelRange = xWb.Sheets("Sheet1").Range("myTable").ListObject

So basically I mention the App before any variable. Also, one thing that might work is to set the variable you mentioned ("myRangeString") as an object. I don't know exactly why, but it happenned to work for me. So instead of:

myRangeString = "$D$20, $C$23"

You would put something alike:

set myRangeString = myWb.Sheet(1).Range("$D$20, $C$23")

Hope it helps.

CodePudding user response：

You code is using late binding which means that the objects are unknown until run-time and there is no IntelliSense. The advantage to using late binding is that the code will run fine for users with older versions of Office. Otherwise it's preferable to user early binding. (See Early and Late Binding.)

Use early binding by setting a reference to the Excel Object Library (in the VB Editor, Tools: References, scroll down and check the box for the Microsoft Excel Object Library)

Here's a working MS Project example of your code using late binding:

Sub ExportToExcelLateBindingCellByCell()

Dim myExcel As Object
Dim myWb As Object

Set myExcel = CreateObject("Excel.Application")
myExcel.Visible = True
Set myWb = myExcel.Workbooks.Add

myWb.Worksheets(1).Range("$D$20") = ActiveCell.Task.Name
myWb.Worksheets(1).Range("$C$23") = ActiveCell.Task.ID

End Sub

Writing to Excel in chunks using arrays is a great idea--perhaps that's what was intended by "$D$20, $C$23". However, the correct range syntax would be to use a colon, but D20:C23 is awkward, use C20:D23 instead.

Here is an early binding example that writes data in chunks, which is noticeably faster for large amounts of data.

Sub ExportToExcelEarlyBindingArray()

Dim myExcel As Excel.Application
Dim myWb As Excel.Workbook

Set myExcel = CreateObject("Excel.Application")
myExcel.Visible = True
Set myWb = myExcel.Workbooks.Add

Dim TaskInfo(4, 2) As Variant
Dim i As Integer
For i = 1 To 4
    TaskInfo(i - 1, 0) = ActiveProject.Tasks(i).ID
    TaskInfo(i - 1, 1) = ActiveProject.Tasks(i).Name
Next i

myWb.Worksheets(1).Range("$D$20:$C$23") = TaskInfo

End Sub

CodePudding user response：

I seem to have solved it more or less at the same as you posted your answers, @orange_guy and @Rachel. The solution was to use semicolon (not colon, and not comma). Thanks a lot anyways!