Hi Adam
I'm sorry about the name mix up – I don't know what gave me the idea that you were Phil!
Your second- and third-to-last messages did not come through in my email for some reason – nevermind – I can see them online. I'll address your questions one by one:
> Re binding: what do the following lines mean an do?
> Debug.Assert xlCenter = -4108
> Const xlCenter = -4108
In my original answer I posted some code like this:
#If EarlyBinding Then
Debug.Assert xlCenter = -4108
Dim oxlApp As Excel.Application
Dim oxlDoc As Excel.Workbook
Dim oXLSheet As Excel.WorkSheet
#Else
Const xlCenter = -4108
Dim oxlLApp As Object
Dim oxlDoc As Object
Dim oxlSheet As Object
#End If
This is using a technique known a conditional compilation. The idea is, at the top of your module, you define a conditional compilation constant like this:
#Const EarlyBinding = True
Then, only the part between the #If and the #Else is looked at by the compiler. The rest may as well not exist. This means that the three object variables are declared as strongly-typed Excel objects, which necessitates a reference to the Excel library (see below**). This gives you two major advantages while developing: (a) you have "intellisense" which means that the VBE will display a drop-down list if valid methods and properties every time you type the name of an object variable followed by a dot and (b) the compiler will check your syntax against the object model and raise any errors if your Excel syntax in incorrect. This is called "early binding" (and has nothing to do with skis!)
However, it is a good idea to remove such helpful references when you distribute your front-end to other computers. This is to avoid problems with other versions of the referenced software being installed. If you remove the reference, then the declarations will fail, which means that you must change all the declarations to "As Object". This is known as "late binding". Conditional compilation allows you to do this in one fell swoop by changing only a single line – the #Const definition becomes:
#Const EarlyBinding = False
This results in the second part of the conditional block (between #Else and #End If) being compiled. Note that we include in here a definition of any constants that we use (e.g. xlCenter) which were defined in the Object Library but are no longer, nor that we have removed the reference. And what of the "Debug.Assert" in the early binding section? This is there just to confirm that the Const definition we are going to use later in the late binding section is correct.
> Why should I put all the Excel VBA code in Access instead of using "xlApp.Application.Run "FileName!MacroName"?
Well, that depends. If you have already written some VBA "macros" in an existing workbook that you are opening and manipulating using automation from Access, then of course you can call them from your automation code. However, it is usually the case that the code is only used when creating the workbook under automation, so it is certainly not appropriate for the code to be in the workbook. In your case, it appears that you are creating then workbook from scratch using TransferSpeadsheet, so the code will not be there to call!
> When do I need to use:
> xlApp.Quit
> Set xlApp = Nothing
> xlBook.Close (or is it xlDoc)
xlBook or xlDoc – it doesn't matter! This is only a variable name – you could call it Cedric of you like. The important thing is that it always refers to an Excel.Workbook.
Use xlApp.Quit if you wish to close the Excel application so you don't leave it running in the background. This will mostly be the case. The only exception is if you want to leave it on control of the user in which case use:
xlApp.UserControl = True
xlApp.Visible = True
It is good practice ALWAYS to set your object variables to Nothing.
> The Range I am referring to is six, not two cells, SumRow,6:SumRow,11
The Range method creates a reference to a contiguous rectangular range by specifying the top-left and bottom-right cells in the range. Therefore, a range from Cell(SumRow, 6) to Cell(SumRow, 11) references six cells, not two.
> Also, in Excel, it lets me define a range as columns("E:E","G:G","I:I").NumberFormat = "$#,##0"
> to format non-consecutive columns, or columns(5,7,9). That doesn't seem to work when I run the VBA from Access. Any idea why?
Range takes up to two arguments. If there are two, then it specifies the top-left and bottom-right cells in the range. If there is only one argument then it should be either a single cell or a text string specifying a range reference that Excel understands. This could be:
* a single cell – Range("C5")
* or a contiguous range – Range("A5:D8")
* or a named range – Range("MyData")
* or a non-contiguous range – Range("A5:D8, F7:G9")
The important thing is that it must be a single string. This should work:
Range("E:E,G:G,I:I")
this doesn't:
Range("E:E","G:G","I:I")
> One more thing. What if I am working with a file with >1 sheet?
> Do I then have to use "Set xlSheet" or ActiveSheet?
You should not use ActiveSheet for the reasons I've outlined. You can either reuse the one Worksheet variable – Set xlSheet = xlDoc("Sheet1"), and then, when you've finished with Sheet1, Set xlSheet – xlDoc("Sheet2"). Or you can declare two different Worksheet variables and set them to the two separate sheets. I prefer the second option as it is clearer.
> The 'Dim xlRng as Excel.Range' doesn't compile. I'm guessing this means I'm missing
> a key library. I don't see any available at File - Options - Add Ins (2010). Nor do I see
> an Access library available in Excel. This is a corp owned computer used in a very
>restrictive environment, I don't have admin rights on the computer, so I may not be
> allowed to download any DLLs.
** The Excel Object Library is in Tools > References (from the VB Editor screen), not File > Options > Add Ins.
Scroll down until you see Microsoft Excel 12.0 Object Library and then click the checkbox. You remove the reference by unchecking the box.
Good Luck!
Graham
_____
Graham Mandeno
Microsoft Access MVP 1996 - 2015
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Saturday, 31 January 2015 11:43
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Error controlling Excel from Access
The 'Dim xlRng as Excel.Range' doesn't compile. I'm guessing this means I'm missing a key library. I don't see any available at File - Options - Add Ins (2010). Nor do I see an Access library available in Excel. This is a corp owned computer used in a very restrictive environment, I don't have admin rights on the computer, so I may not be allowed to download any DLLs.
Am I stuck?
Adam
[Non-text portions of this message have been removed]
Posted by: "Graham Mandeno" <graham@mandeno.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (19) |
Tidak ada komentar:
Posting Komentar