Search:

Type: Posts; User: VBAhack

Page 1 of 13 1 2 3 4

Search: Search took 0.31 seconds.

  1. Replies
    4
    Views
    8,687

    Re: How to validate an INSERT, after Executing

    If you don't mind my asking, where did you get the SQLite3 ODBC Driver?

    I get the following runtime error:



    I'm trying this with VBA/Excel.
  2. Re: ADODB/Excel VBA to aggregate data and create report

    Third time must be a charm in terms of code streamlining. Discovered how to use TRANSFORM and PIVOT to aggregate the results to the final form I wanted and copy directly to the worksheet using...
  3. Re: ADODB/Excel VBA to aggregate data

    After stumbling on an example using UNION to create a single query to multiple data sources (see below link), I accepted the challenge to streamline my project to eliminate separate queries to each...
  4. Thread: calculating Pi

    by VBAhack
    Replies
    4
    Views
    4,453

    Re: calculating Pi

    You need to use software that has extended precision capability, like the Python mpmath module, or FLINT:

    https://mpmath.org/
    https://flintlib.org/

    Years ago there was an extended precision...
  5. Re: ADODB/Excel VBA to aggregate data

    In the end I figured out a way to implement my original idea. Key steps were:

    (1) defining the data range in each workbook by updating a named range with the BeforeSave() event

    (2) aggregating...
  6. [RESOLVED] ADODB/Excel VBA to aggregate data and create report

    Trying to use ADODB with Excel VBA (Office 2021) and connection strings to aggregate expense data (date, category, amount) from ~6 closed Excel workbooks into a summary showing amount by category for...
  7. Replies
    30
    Views
    46,111

    Re: Cubic Spline Tutorial

    Hopefully you've found an answer by now, but if not, I think the best way to answer is by a simple example. Attached are the equations for (1) natural boundary conditions per explanation in my...
  8. Replies
    1
    Views
    1,865

    Detect Timeout?

    I have an Excel sheet with a simple macro that uploads the information on the sheet to a local Access DB.


    Dim cn As Object
    Dim rs1 As String, scn As String, dbWb As String, dsh As String
    ...
  9. MsOf10 Re: display Userform on second monitor

    Ah, I found the problem. I had another routine that was "validating" the value of userform.Left and userform.Top and was setting negative values to zero. When I removed the validation, everything...
  10. MsOf10 Re: display Userform on second monitor

    Hmm, no difference. Sheet is on the secondary monitor but userform is still on the primary monitor. I was using:


    Me.Left=(Application.Width - Me.Width) / 2
  11. MsOf10 [RESOLVED] display Userform on second monitor

    I'm sure this has been asked before, but I can't seem to find any posts that address it. A user has a dual monitor system and always opens Excel files on his SECONDARY monitor. When the Excel...
  12. Replies
    6
    Views
    2,939

    MsOf13 Re: [RESOLVED] Bizarre Excel VBA Overflow error

    The routine is used by others to look for open sheets that fit a certain data pattern. I have no control over what additional (non project related) sheets the user might have open, thus I have a...
  13. Replies
    6
    Views
    2,939

    MsOf13 Re: Bizarre Excel VBA Overflow error

    Well, I've figured out what the problem is, but the workaround isn't very pleasant. I didn't know this, but when you read a cell's value with VBA, the default quantity captured is the FORMATTED...
  14. Replies
    6
    Views
    2,939

    MsOf13 Re: Bizarre Excel VBA Overflow error

    I've since determined there is something about the sheet itself that's the problem. Actually, upon further investigation, there are several sheets (maybe 8 out of 20) in the same workbook that...
  15. Replies
    6
    Views
    2,939

    MsOf13 [RESOLVED] Bizarre Excel VBA Overflow error

    I have a loop routine that loads a small rectangular range (36 x 12 cells) into a variant (array) for processing. This is done for every worksheet of every open workbook. For some reason, I get an...
  16. Replies
    3
    Views
    681

    Re: SELECT non NULL records, then sort them

    I tried that also. Same result. The sorting by XX doesn't make sense. Here is example of the result (showing XX only)


    100.
    100.
    2.
    48.
    90.
    95.
    99.
  17. Replies
    3
    Views
    681

    SELECT non NULL records, then sort them

    Having trouble sorting records (using ADO with Excel VBA on Access DB file). The field I'm interested in can have numeric or NULL values. I only want the records with non-NULL values for field XX...
  18. Replies
    2
    Views
    1,427

    Re: BOF/EOF Error using GetRows() with 1 record

    Thanks for your reply. I tried a test and got the same result as you, which means I must somehow be incrementing the recordset pointer to EOF. I now know what to look for.


    Sub testADO()
    ...
  19. Replies
    2
    Views
    1,427

    BOF/EOF Error using GetRows() with 1 record

    Using ADO within Excel (2013) VBA to extract records from an Access file. For some reason I get a BOF/EOF error when using GetRows() if only one record (i.e. RecordCount = 1) is in the recordset. ...
  20. Replies
    2
    Views
    922

    MsOf10 Re: Excel VBA Class module

    Thanks! I figured it must be something simple...
  21. Replies
    2
    Views
    922

    MsOf10 [RESOLVED] Excel VBA Class module

    Can someone tell me why the variable i is not set in the below example? Are Class methods unable to modify the values of input variables?

    In normal code module:

    Sub test_SimpleClass()
    Dim...
  22. Replies
    3
    Views
    931

    MsOf10 Re: How to debug plotting error

    Data is stored in worksheet cells and is visible. That's how I know nothing is negative or zero.
  23. Replies
    3
    Views
    931

    MsOf10 How to debug plotting error

    Using VBA modules in a data analysis application to compute statistical results that I update to an Excel worksheet with existing plots (i.e. the plots aren't created each time but automatically...
  24. Replies
    6
    Views
    2,956

    MsOf10 Re: Excel hangs on workbook open

    Yeah, you are right. Thanks for the thoughtful posts. I guess I should be happy that I was able to recover and not lose the 4+ hours of work. I have to keep reminding myself that the tools we use...
  25. Replies
    6
    Views
    2,956

    MsOf10 Re: Excel hangs on workbook open

    I've saved a copy of the failing workbook for debug purposes. There are no duplicate worksheet names. It has been many months since I've added any worksheets to the workbook and/or changed any...
  26. Replies
    6
    Views
    2,956

    MsOf10 Re: Excel hangs on workbook open

    Workbook_Open() saves Excel calculation mode and MoveAfterReturnDirection values to worksheet cells, changes the calculation and MoveAfterReturnDirection values, and sets several range and global...
  27. Replies
    6
    Views
    2,956

    MsOf10 [RESOLVED] Excel hangs on workbook open

    I have a relatively complicated Excel application with maybe 30 routines and 100's of lines of VBA code. During development over the past several months, I diligently saved different versions, and...
  28. Replies
    0
    Views
    1,659

    ADO read xlsx with HDR=No

    Trying to use ADO in Excel VBA to open an xlsx file with HDR=No. When I try to set HDR=NO I get a runtiime error. What am I doing wrong?


    Sub TestADO()
    ...
  29. MsOf10 Re: Excel - combo chart scatter and column!

    Found a hack elsewhere by making the 2nd series a scatter plot and adding vertical error bars (100% negative and 0% positive). Thus, the columns are really negative vertical error bars. That makes...
  30. MsOf10 Re: Excel - combo chart scatter and column!

    Nope. The attached illustrates. I want to use the exact same data (x and y values) for both series. In the left plot, everything lines up but the x-values aren't scaled properly (because I used a...
  31. MsOf10 Re: Excel - combo chart scatter and column!

    Thanks for your replies. Yep, already have the same x-axis values for each series. Also, already saw that link. Every example I've seen combining scatter with column has linear data for the...
  32. MsOf10 [RESOLVED] Excel - combo chart scatter and column!

    Anybody know how to make a combo scatter + column chart so the x-values line up?

    The fundamental problem is that a column chart behaves like a "line" chart in that the x-axis corresponds to...
  33. Replies
    3
    Views
    908

    Re: using Jet with text (csv) file

    Would someone be willing to try the code in the first post to see if they get the same result (i.e. filename with a period doesn't work)? I'm surprised there would be such a limitation.
  34. Replies
    3
    Views
    908

    Re: using Jet with text (csv) file

    Location isn't the issue. I originally had the files in a folder. I only put them in the root for simplicity for the post.
  35. Replies
    3
    Views
    908

    using Jet with text (csv) file

    Am I doing something wrong or are file names that include a period illegal? I have two identical csv files (PhoneList.csv and Phone.List.csv) but the latter one can't be opened using ADO and Jet.
    ...
  36. Replies
    2
    Views
    781

    Re: ADO to update MDB records

    Thanks, I understand now.
  37. Replies
    2
    Views
    781

    [RESOLVED] ADO to update MDB records

    I've been using Excel VBA and ADO to practice SELECTing and FILTERing records from an MDB file. I used the following code to change values in certain records and was surprised that the MDB file had...
  38. MsOf10 Re: Trouble with Excel Userform with ComboBox

    Thanks for your reply. Finally figured it out. I didn't realize setting the ListIndex causes ComboBox1_Change() to fire. The issue was I was using Unload UserForm1 at the end of...
  39. MsOf10 Re: Trouble with Excel Userform with ComboBox

    Sorry, I don't follow. If I comment out the .Show line, the Userform never appears. I only want the form to be visible when the user clicks on a certain cell, hidden otherwise. Once the form...
  40. MsOf10 [RESOLVED] Trouble with Excel Userform with ComboBox

    I have a Userform with a single ComboBox control that displays when a certain cell is clicked. Everything works fine except I want the first value in the list to be displayed in the box (as opposed...
Results 1 to 40 of 500
Page 1 of 13 1 2 3 4



Click Here to Expand Forum to Full Width