Exporting the access resultset to excel programmatically

Visual Basic for Applications (VBA)

Index ‹ Microsoft ISV

- Previous
  - 1
    - Highlighting cells within a worksheet Several of us in our office have a fantasy golf league and we administrate it off-line using an excel spreadsheet (we haven't found a free website that lets us tailor the rules to the way we like it.) Anyway, it is a salary cap format, meaning that only one golfer can be on one team at a time. To keep track of this, one of our worksheets has a list of all the golfers. That same worksheet also has every team's roster. For all golfers that are on someone's roster, I manually highlight that same name in the master list of golfers so it is easy to see who is taken and who is available. What I'd like to be able to do is have it automatically highlight that golfer's name in the master list when it appears in the individual team roster's page (and conversely un-highlight the name when the player is dropped). But I don't know what the VBA code should be. Below is an example of how it looks: you can see the master list on the left and the team rosters on the right. Any help would be greatly appreciated. Thanks! Tag: Exporting the access resultset to excel programmatically
  - 2
    - ISV Software Requirements Hello all, Sorry I'm new here and was unable to find an answer to my questions using the search feature. Because Microsoft requires several MCPs to satisfy their non-ISV competancies, I need to take the ISV route to get Certified Partner status. If I was to go for the ISV Competancy, what kind of application are they expecting me to develop I read on the lionbridge site that games and office productivity applications were not allowed for "Certified for" testing. What would I develop then Would an alarm clock be adequate providing it met the other operational requirements Thank you, -Bunny Tag: Exporting the access resultset to excel programmatically
  - 3
    - iexplore.exe - Application Error I get the following error message when I close my internet explorer. The instruction at "0x62304320" referenced memory at "0x62304320", The memory could not be "read". Click on OK to terminate that program. Any ideas what is causing this Tag: Exporting the access resultset to excel programmatically
  - 4
    - Replacing (changing) the pictures in PowerPoint presentation I am trying to write a small program to replace pictures in PowerPoint presentation... To find the pictures, I check Shape Type (Shape.Type = msoPicture)... But the problem is: how can I change the picture after this step What is the method I should use to load the new picture from file to the Shape Best regards and have a nice day, Hani Tag: Exporting the access resultset to excel programmatically
  - 5
    - Using Inputbox to detect a worksheet Never mind I solved it, see red marked for solution Hello, I want to let the user add a serie to a chart. While doing so the usr will collect data using Inpubox from a different worksheet. He will need to go there three times to collect all data. I would therefore want the code to recognize on which worksheet the usr collected the first data set on and then when the usr has collected the x-values I want the macro to return to that sheet so the usr can collect the Y-values and finally return to collect the DataLabels. I have earlier here been tiped of Dim ws As Worksheet Set ws = ActiveSheet code... ws.Activate which has worked well earlier but now it lets me down. Can anyone help me with this Below is the code to collect all data. The InputBoxes appear on the sheet containing the chart and the usr has to manually switch sheet to the one with the data. Dim Ch As Chart Set Ch = ActiveChart Dim ws As Worksheet ''' Collect X and Y values Set myXData = Application.InputBox( _ prompt:="Select a range containing the X data.", _ Title:="Select Chart Data", Type:=8) ''' here I want the code to recognize on which sheet the x data are collected '''from. And then return to this sheet to collect y data Set ws = myXData.Worksheet ws.Activate Set myYData = Application.InputBox( _ prompt:="Select a range containing the Y data.", _ Title:="Select Chart Data", Type:=8) ws.Activate ''' Collect legends Application.DisplayAlerts = False Set myLegends = Application.InputBox( _ prompt:="Select legends if wanted else press cancel", _ Title:="Select Data Labels", Type:=8) Ch.Activate Now I have figured that Set ws = myXData.Worksheet gives the worksheet. I have used this before the last to green marked lines which lets the wsheet with data remain as the active. However with that I can't make the sheet with the chart the active sheet again. Any suggestion on this Grateful for help with this. \Jonas Tag: Exporting the access resultset to excel programmatically
  - 6
    - Excel Macro running slower in 2003 vs. 2002 I am trying to figure out why a macro runs slower in Excel 2003 vs. 2002 and narrowed it down to a function that deletes rows. This function is used numerous times and before this function is called a varying row is selected, then is processed in this function. I think the Selection.Delete command is causing the slowed performance but not sure why it is. Does anyone have a suggestion Up to 100 rows can be selected based upon the criteria. CellCount = 0 For Each Cell In Selection If Cell.Value = 0 Then CellCount = CellCount + 1 Next Cell If CellCount = 0 Then Exit Sub CellCount = CellCount - 1 ReDim DRows(CellCount) As Single TicCounter = 0 For Each Cell In Selection If Cell.Value = 0 Then DRows(TicCounter) = Cell.Row If Cell.Value = 0 Then TicCounter = TicCounter + 1 Next Cell Set DeleteRange = Rows(DRows(0)) DeleteRange.Select For NCTS = 1 To CellCount Set DeleteRange = Union(DeleteRange, Rows(DRows(NCTS))) DeleteRange.Select Next NCTS DeleteRange.Select Selection.Delete Shift:=xlUp Tag: Exporting the access resultset to excel programmatically
  - 7
    - ReportFault() vs. WerReportCreate/WerReportSubmit() on Vista Hi all, I'm experimenting with the available APIs for error reporting. On XP systems, I'm using ReportFault(), which seems to work as expected- it displays the WER dialogs, sends data to the Winqual site (including minidump data), launches a debugger when asked for by the user etc. Is this the right place to ask questions about those APIs If not, please ignore the following. (But if you know where I should ask instead, please drop me a note, thanks!) On Vista, ReportFault()'s behavior changed, unfortunately. With the default error reporting settings (automatic reporting without asking the user), it sends the crash report to the Winqual site - and then terminates the application. However, in my particular case, I need to re-gain control after WER reporting in my application in order to perform additional logging and clean-up, and to provide users with an option to continue working for a limited time (for example, to try and save their current data). Since ReportFault() didn't do what I wanted, I tried the WerReportCreate/WerReportSubmit combo instead. Those APIs do indeed return and don't terminate the application - but they also don't seem to report all the crash details correctly. For example, I've never seen any indication that one of the crash reports produced by the WerReport* APIs contained minidump information (judging from the problem report history in wercon.exe). My first guess is that I'm simply not using the WerReport* APIs correctly - but after trying lots of different options and variations, I'm pretty much at a loss. Hence my questions: - Where can I find tested sample code which uses WerReportCreate/WerReportSubmit() - Is there a way to use ReportFault() under Vista without terminating the application Thanks! Claus Brod Tag: Exporting the access resultset to excel programmatically
  - 8
    - height of a field in word Hi, Any idea as to how we can get the height of a field in word in vba Thank you. Tag: Exporting the access resultset to excel programmatically
  - 9
    - Problen reading Cell Im from Brazil, and my regional seeting are "," to separete decimal places But when i use VBA to read a cell value, if it is 0,93 he only reads 0, but if it is 0.93, he reads correctly this is a sample of the code that i'm using Code Snippet Dim Proj(500, 500) As Double Proj(3, L) = Val(Cells(21 + L, "N").Value) My regional settings are ok Tag: Exporting the access resultset to excel programmatically
  - 10
    - Bloomberg + Execel = True? Ok my situation is like this: I have a computer that runs Windows XP Professional. On the computer I have a Bloomberg subscription i.e. I have an ordinary monitor, but a Bloomberg keyboard and it is "true" Bloomberg subscription (it is not the Bloomberg website I am referring to). I have an Excel spreadsheet that is connected to Bloomberg and updated once every day using a DDE connection. Now will this work even if the Bloomberg program is closed If not so is there any other way of creating a link that works even if the Bloomberg program is closed The connection works through a macro that runs on a prespecified time every day. The code for opening the Bloomberg link is: channelNumber = Application.DDEInitiate(app:="BLP", topic:="S") (the code for the macro is not written by me) Please if you know anything about this help me out. I have contacted the Bloomberg helpdesk by they never seem to give a staright answer. Thank you! Tag: Exporting the access resultset to excel programmatically
  - 11
    - ApplyCustomType question I'm running Office XP with current updates, and I'm observing some peculiar behavior when I try to use ActiveChart.ApplyCustomType in Visual Basic. The FIRST time I run my Macro after opening Excel, none of the code after: ActiveChart.ApplyCustomType xlUserDefined, "name" executes. After the first time I run the macro, the VBA Project (XLUSRGAL.XLS) appears in the "Project" window of the Editor, though none of the custom charts I've placed in that file are listed. After this the macro runs perfectly every time, until the next time I close and open Excel. The macro also runs correctly the first time if I open XLUSRGALS.XLS in another window BEFORE I run the macro. I see identical behavior when I use xlAnyGallery and xlBuiltIn (replacing XLUSRGALS.XLS with XL8GALRY.XLS). It doesn't make any difference which of the user-defined or built-in custom charts I call. Does anybody know what's going on I want my code to work the FIRST time! Thanks, Alexander Tag: Exporting the access resultset to excel programmatically
  - 12
    - opc I am trying to develop an opc - da client using .net technology. Have gone through the documents on opc and opc client and all, but am still not sure how to implement it, can someone give me a direction on how to start with it Tag: Exporting the access resultset to excel programmatically
  - 13
    - Issue with Selection.TypeText and Chinese Characters. Hello Everyone, I'm having trouble getting chinese characters to work with TypeText. When I use English, everything works great. For example, the following statement shows up as " " in my word document. exec sp_OAMethod @objword, 'Selection.TypeText("ГА‡шї‚Ѕy")' While I'm using T-SQL and not VBA, I figured that the people here would probably know the answer. I've been finding VBA documentation or recording word macros and translating it to work with T-SQL as there is hardly anything on intergrating Word and SQL Server. So, my appologies in advance if this is the wrong forum. If anyone knows the answer or could point me in the right direction, it'd be greatly appreciated. Thanks! Tag: Exporting the access resultset to excel programmatically
  - 14
    - DoCmd.OpenReport passinh param to underlying query in MSAccess Hi all, - I have in Access (2003) a report who's datasource is a saved query. - This query needs a param How can pass the param to the query using the DoCmd.OpenReport method Any help is highly appreciated. TIA Dan Tag: Exporting the access resultset to excel programmatically
  - 15
    - "Cabs requested" icon Hi, a number of the crash reports we're seeing is marked with the "Cabs requested" icon (cab icon with a little clock in it). The documentation says that this icon means that ".cab data has been requested and is not yet ready for download". I'm not quite sure how I should read this. My current interpretation is as follows: For some crash reports, the .cab file is not transmitted to the Winqual site by default. Only a short notification about the crash is recorded. Crash reports without .cab data are marked with a .cab icon with a red "x" in it. On the Winqual site, we can request more data for such crash reports. The docs say: "When end-users experience the event, Microsoft will ask to collect minidump data." I guess this means that if another crash of the same type (same bucket) occurs on a customer system, the Winqual server will request the .cab data immediately from them. However, if the same crash never occurs again, we will never see any .cab data. Is this an adequate interpretation Apparently, some crash reports have .cab files by default, i.e. without us explicitly requesting those files. What are the criteria for the Winqual server to request .cab data even for the first crash of a new event type Thanks! Claus http://www.clausbrod.de/Blog Tag: Exporting the access resultset to excel programmatically
  - 16
    - Save As event not firing in Office 2007 I wrote an event for Save As menu , it works fine for Office 2003, but its not working for Office 2007 (1 line of code below) If evtFileSaveAs Is Nothing Then Set evtFileSaveAs = 'Application.CommandBars("Menu Bar").FindControl(, 748, , True, True) I am expecting doubt like Save As Menu Id is not 748. Is there any solution for this Thanks in advance and help appriciated. Tag: Exporting the access resultset to excel programmatically
- Next
  - 1
    - Excel 'Freeze panes' from Access VBA (Off2000) Hi, I'm searching for a way to freeze the upper part of my Excel sheet (with headers and such, like the 'freeze panes' menu option in Excel), from my Access VBA code. I've been searching for a while but didn't come up with something usefull. Anyone any suggestions Thanks in advance, I'd be really glad if someone knows this! Tag: Exporting the access resultset to excel programmatically
  - 2
    - Message for display during macro execution I have a macro that takes a long time to execute. While it is running I would like to put an object on screen to inform the user that the macro is running and they should just wait. Otherwise they may think something is wrong. It would also be good if this object had a button on it the user could click to end the macro if they run out of patience. I thought about a message box, but when it is displayed it halts execution until the user clicks it, then it dissapears anyway so this will not do the job. Is there another object I could place on screen that has say "display" and "close" functions that I could use as follows; Sub theMacro() SignalBox.display "Just wait, the macro is running" 'lots of code 'lots of code SignalBox.close UserForm1.show ' shows macro results End Sub Maybe another userform could do this, but it would have to be able to stop the macro via a command button (not sure if possible) and/or close itself. Tag: Exporting the access resultset to excel programmatically
  - 3
    - Upgrade problem Access 97 and Access 2003 on same machine Hi We have finally started to upgrade to Office 2003, I still have a copy of Access 97 on my machine to maintain databases which cannot be upgraded until the roll out is finished. However, when I run my code now I am getting a problem. Access 97 now will not use the Trim$ function is an SQL statement, I get a message that the function is not available. Trim$ will work outside of an SQL statement e.g. Debug.print Trim$(" some text ") Any ideas where my problem stems from Tag: Exporting the access resultset to excel programmatically
  - 4
    - How to check if a date exists Could anyone point me in the right direction I just need a way to see if a date exists...example: "6/31/07" doesn't exist, whereas "6/30/07" does exist. Thanks! Tag: Exporting the access resultset to excel programmatically
  - 5
    - Clearing a manually created indent for a paragraph I am testing the process of removing indents from headings. I created a manual tab indent (pressed Tab and created an indent for the heading text). Now I want to remove this indent by code. I tried the following: oPara.Outdent oPara.TabIndent(-1) oPara.TabStops.ClearAll The above codes were tried individually and collectively. I was not able to clear the indent created. Howeve, if I create an indent by pressing the 'Indent' icon on the menu bar, I could clear it by oPara.Outdent. If I create an indent by code e.g. oPara.TabIndent(1), I could clear it by the code oPara.TabIndent(-1). Can anyone suggest how best I can remove a manually created Tab indent by code Thanks in advance. Tag: Exporting the access resultset to excel programmatically
  - 6
    - BSM 2005 Server Installation Failed Hi All We are trying to install Business Scorecard Manager 2005 Single Server Installation. We are getting error "Failed Microsoft Sharepoint Extended Site". We can able to create a new website and extend another web site from Central Administration. Please help me how to fix this error Tag: Exporting the access resultset to excel programmatically
  - 7
    - Internet Explorer 7 jpeg and gif blurry after tools/internet options/advance/reset I was working on another problem and reset my advanced options. After all my jpeg and gifs now are fuzzy when displaying in only internet explorer 7.0.5730.11 all other programs display normal. Tag: Exporting the access resultset to excel programmatically
  - 8
    - API and OpenGL in VBA for Excel How to use in VBA for Excel such API functions like, SetPixel, GetPixel, LineTo, PolyLine or ArcTo acting on the new created standard UserForm or in the special new window opened by appropriate API function. I know that it is trivial within VB applications, whereas VBA makes troubles (handle address of UserForm). I would be grateful for some simple examples (which have been tested and work well). My second question is: How one can use OpenGL library in VBA (as far as I know it is possible in the case of VB) Thank you in advance Spinor Tag: Exporting the access resultset to excel programmatically
  - 9
    - Referencing objects from different reports Hi, I have 2 Access Forms, Frm1 & Frm2. In Frm1 I have a cmd button which when clicked should open Frm2 and refresh a combo box in Frm2. How can this be done as the below is not working. Private Sub cmd_cmp_Click() DoCmd.OpenForm "frm2" Me.frm2.Form.cbo_1.Refresh End Sub Tag: Exporting the access resultset to excel programmatically
  - 10
    - Saving a pic hey i really need help with this, i have posted in tons of forums and the descussion groups, no replies! gar lol, well i have this macro in excel 2003 and it has a userform and there are a few elements (3 pictures, 3 text boxes) that get modified during the macro then i need to kinda merge them and save it as one picture, can a macro do that any help would be great thx Tag: Exporting the access resultset to excel programmatically
  - 11
    - Error with Excel Application Object Hello ladies and gentlmen, this is a very specific error I'm dealing with, and one of the stranger ones I've run across. First of all, I am attempting to open an excel file from MS Access using an Excel Application Object. If Excel is not open, the file will open fine. If Excel is open and there is no workbook open, the file will open fine. If Excel is open and there is a workbook open, then the file will not open. Here is the code I'm using. Set appExcel = GetObject(strFilePath & strFolder & "\" & strFileName) appExcel.Application.Visible = True If blnIsWorkbookOpen(strFileName) = True Then appExcel.Application.Workbooks.Open strFilePath & strFolder & "\" & strFileName Else appExcel.Parent.Windows(1).Visible = True End If Set appExcel = Nothing The thing of it is, when the database is not locked against user modification, the code works fine. The other sticky bit is that it works fine in a database that is even more heavily locked than this database. The question is then, how do I get the code to work correctly when the DB is locked Tag: Exporting the access resultset to excel programmatically
  - 12
    - Obtain Unique Work Units via SQL Below is the SQL I have for a Query. The results of my query are below the code. Everything works great except for the WorkUnit totals. I am getting the total Records for the date range and I really need the Unique workUnits for the date range. For this particular daterange I have a total of 197 WorkUnits but in reality there really are only 89 Unique WorkUnits. In the example below on one date range I had 0 No Faults and on the other date range I had 7 No Faults. Can anyone tell me how to add to this query to get only Unique WorkUnits SELECT 'No Faults' AS [Fault Type], Sum(IIf([FaultCategory]='No Faults',1,0)) AS [NoFault Totals], Count (WorkUnitsFaultsMainTBL.WorkUnit) AS [Total Work Units], FormatPercent(Sum(IIf([FaultCategory]='No Faults',1,0))/Count([WorkUnit]),2) AS [Pct NoFaults] FROM WorkUnitsFaultsMainTBL WHERE (((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174")) And ((WorkUnitsFaultsMainTBL.TodaysDate) Between Forms!Queries_ReportsFRM!StartDateTxt And Forms!Queries_ReportsFRM!EndDateTxt)); Fault Type--------NoFault Totals----------Total Work Units No Faults-----------------0--------------------------197 Fault Type--------NoFault Totals----------Total Work Units No Faults-----------------7--------------------------197 Tag: Exporting the access resultset to excel programmatically
  - 13
    - Copying Named Ranges I have a worksheet with a number of named cells (so that I can put data in them or retrieve data from them with VBA and not have to change anything should rows / columns be added or removed). I have a sort of template file from which I copy and paste the spreadsheet with all the named ranges to create a series of new files. The problem is, the cell names do not copy, so they do not appear in any of my new files. I also notice that on my original file, the range names are defined locally, like with ="Sheet1!$U$33". I imagine that if the sheet was to be renamed something other than Sheet1, I would have a problem. So I guess my question is, a) How can I name ranges in such a way that the name will stick even when I copy and paste the sheet to other workbooks. b) How can I go about naming ranges relative to the active spreadsheet, such that calling Range("my Range name") will always work no matter what spreadsheet I am in, so long as there is such a named range in the worksheet. Thanks! Tag: Exporting the access resultset to excel programmatically
  - 14
    - how to code an EDI transation with VBA Hi, I am looking for some documentation and advise on how to handle EDI within an Access2002 (VBA) app. Thanks if anyone can provide feedback, Jay Tag: Exporting the access resultset to excel programmatically
  - 15
    - Date set and iteration I want to create a spreadsheet to go to the first day of the current month and set a date if the day is a weekday and not a weekend(or holiday if that is possible). I will also want to check against the sheets in the spreadsheet as I have sheets numbered 1 through 31 and will be hiding each day that is not a regular business day. I basically just need to know how to find the current day in the month and iterate through it as well as set the format for the date in excel. Tag: Exporting the access resultset to excel programmatically
  - 16
    - System.Web.UI.WebControls not serializible in Web Parts (MOSS 2007) I create System.web.ui.webcontrols in CreateChildControls and I get error messages that they cannot be serialized. Scott Hillier's book shows examples of this yet I cannot get them to work. Thanks Tag: Exporting the access resultset to excel programmatically

Alex Kurama

Dear all,

I would know how can I export the access resultset to excel. Can I get the query from Access which I have done and saved in access directly, so that I do not need to rewrite the SQL statement in the VB, if yet, then how I think I need to use something like

Set rs = db.OpenRecordset("Query name") or such

but I don't know how. Can anyone of you can help me in this issue Thanks in advance.

Regards,

Alex

Top

Re: Exporting the access resultset to excel programmatically

Shasur

You can use the CopyFromRecordset method for that:

Code Snippet

sQuery = "Select * From Products"

RS.ActiveConnection = Cn
RS.Source = sQuery
RS.Open

If RS.EOF = True And RS.BOF = True Then
'Cells(i1, iRevCol).Value = "N/A"
GoTo TakeNextRecord
End If

RS.MoveFirst
Activeworkbook.Sheets(1).Range("A2").CopyFromRecordset RS

Cheers

Shasur

Top