Search:
Type: Posts; User: VBAhack
Search:
Search took 0.31 seconds.
-
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.
-
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...
-
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...
-
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...
-
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...
-
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...
-
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...
-
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
...
-
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...
-
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
-
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...
-
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...
-
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...
-
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...
-
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...
-
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.
-
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...
-
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()
...
-
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. ...
-
Thanks! I figured it must be something simple...
-
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...
-
Data is stored in worksheet cells and is visible. That's how I know nothing is negative or zero.
-
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...
-
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...
-
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...
-
Workbook_Open() saves Excel calculation mode and MoveAfterReturnDirection values to worksheet cells, changes the calculation and MoveAfterReturnDirection values, and sets several range and global...
-
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...
-
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()
...
-
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...
-
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...
-
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...
-
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...
-
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.
-
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.
-
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.
...
-
Thanks, I understand now.
-
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...
-
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...
-
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...
-
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...
|
Click Here to Expand Forum to Full Width
|