VBA Help

What VBA do I need that will look at two strings and show a message box stating which is larger?

Example:
1) String1 = "1-668!a" String2 = "1-668!b"
2) String1 = "288$4$22" String2 = "287$4$22"
3) String1 = "100(4)(57)" String2 = "100(4)(577)"

1) String 2 is larger
2) String 1 is larger
3) String 2 is larger

By larger I mean which will be listed last if sorted in ascending order.

5 Comments

PMI ACP® Project Management

LVL 12

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Need help with the following VBA coding that has a variable that comes up empty.

I'm trying to convert from reading field values directly to text box - Me.Control values - but the dblWorkHours variable comes up empty or blank in the second version.

Currently - dblWorkHours will come up and read the correct values with this version:

Set rstTemp = CurrentDb.OpenRecordset("tempTimesheetWeek")
   With rstTemp
      .MoveLast
      .MoveFirst
      lngCount = .RecordCount
      ''debug.Print lngCount & " records to write"
      
      If lngCount > 0 Then
         'Attempt to find matching record in tblTimeSheetData
         'Create or edit one record in tblTimeSheetData for each weekday
         'that has hours worked
         Do While Not .EOF
         
            lngJobTicketID = Nz(![JobTicket], 0)
            lTimeTypeID = Nz(![TimeTypeID])
            If lTimeTypeID = 0 Then lTimeTypeID = 1
            sDesc = Nz(![WorkDescription], "")
            lProjectID = Nz(![JobTicket])
            
            For i = 1 To 8
            
              Select Case i
              
              Case 1
                'if value is blank ..
                'don't disturb the hours for that previous week also
                
                dblWorkHours = Nz(![01WorkHours])
              
              Case 2
                dblWorkHours = Nz(![02WorkHours])
              
              Case 3
                dblWorkHours = Nz(![03WorkHours])

Open in new window

…

3 Comments

Hi guys

I've got an Excel sheet with around 12 columns and around 34 rows of content. I want to be able to do a mail merge, so that the content of 4 out of 12 of these columns and all of the rows can be sent to our ticketing system to create a separate ticket for each row.

Is there a way to do this?

Thanks for helping
Yash

3 Comments

I am developing an employee database. I added all employees to an organisational unit (department etc) - and each organisational unit belongs to another organisational unit, except for the top one which is blank; the CEO. With a self-join, I can then create a hierarchy once all the data has been added.

I tried creating a function which would populate a collection from the uppermost organisational unit (the CEO) all the way down and I used recursions to traverse the tree.

Function preorderProcessing(parentID As Integer)

    Dim MyDB            As DAO.Database
    Dim MyRS            As DAO.Recordset
    Dim x               As Integer
    
    Set MyDB = CurrentDb()
    Dim qdf As DAO.QueryDef
    Set colOrgUnits = New Collection
    
    If parentID = 0 Then
        Set qdf = MyDB.QueryDefs("qyrOrgUnits_SearchParentID_Null")
    Else
        Set qdf = MyDB.QueryDefs("qryOrgUnits_SearchParentID")
        qdf.Parameters("parentID") = parentID
    End If
    
    Dim rs As DAO.Recordset
    Set rs = qdf.OpenRecordset
    
    Do While Not rs.EOF
      colOrgUnits.Add (rs.Fields("org_OrganisationUnitID"))
      rs.MoveNext
    Loop
    rs.Close
    
    'debug
    Debug.Print "Now checking units: "
    For x = 1 To colOrgUnits.Count
        Debug.Print "In this stack: " & colOrgUnits.Item(x)
    Next x
    
    Debug.Print "COUNT: " & colOrgUnits.Count
    If colOrgUnits.Count > 0 Then
        For x = 1 To colOrgUnits.Count

Open in new window

…

6 Comments

Getting ERROR in my SUBTotals at the FORM FOOTER. Please let me know what need to be done. Also how I can pass value from ComboBox to Query.
For example I want all the students that are [TRAINING] from ComboBox between [StartDate] AND [END DATE]

Please review attached pic's for more details

Form is getting data from query
StudentPassForm.PNG
StudentPassForm1.PNG
Query-Profitloss-.PNG

6 Comments

How do I get data from a closed excel file into an outlook VBA collection?

The size of Sheet1 is N-row by M-column and data is sparsely populated.

N is not fixed (changing as rows are added to or deleted from the end of the Sheet1).

I would like to read data (including empty cells) from the entire N x M range.

The total number of collections is M and this number is fixed.

Kindly provide VBA code if possible.

Thank you.
Thomas

12 Comments

How can I change the below code into a function so that I can just plug in the following information?

Variables to substitute in code
ActiveSheet = Sheet name
PivotTable = Pivot table name
Value = Field name
Columns = Field name
Rows = Field name
Filter = Field name

Sub Rebuild_PivotTable()

    ActiveSheet.PivotTables("PivotTable3").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Open Raw Data", _
        Version:=6)
        
    'Clear all pivot table fields
    ActiveSheet.PivotTables("PivotTable3").ClearTable
    
    'Add to values field
    ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
        "PivotTable3").PivotFields("Incident ID"), "Count of Incident ID", xlCount
    
    'Add to Columns field
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Status")
        .Orientation = xlColumnField
        .Position = 1
    End With
    
    'Add to Rows field
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Site")
        .Orientation = xlRowField
        .Position = 1
    End With
    
    'Add to Filters field
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("On Report")
        .Orientation = xlPageField
        .Position = 1
    End With
    'Apply filter
    ActiveSheet.PivotTables("PivotTable3").PivotFields("On Report").CurrentPage = _
        "TRUE"
End Sub

Open in new window

10 Comments

Question by

upobDaPlaya

3d agoSolved

Excel VBA Move Files via FSO

I want to loop thru a folder with FSO similiar to the below code, however if the criteria is met
#1 I want to MOVE the item to a folder called OLD and
#2 If for some reason the file is open I do NOT want to attempt to move the file..so I assume I need to do an ONERROR prior to the move, but how should I structure my ONERRor ?

'Reference to Microsoft Scripting RunTime Library must be added

Sub FsoExample()
Dim fso As FileSystemObject
Dim srcFolder As Folder
Dim xlFile As File
Dim FolderPath As String

FolderPath = "C:\Test"      'Source Folder Path
Set fso = New FileSystemObject

Set srcFolder = fso.GetFolder(FolderPath)   'Setting Source Folder

'Looping through all the files in the source folder
For Each xlFile In srcFolder.Files
    'Checking if the file in the source folder is an Excel file
    If fso.GetExtensionName(xlFile) = "xlsx" Then 'If the Excel file has .xlsx extension
        'Do whatever you want to do with that file here
        'Say you want to open the file then try this...
        Dim wb As Workbook
        Set wb = Workbooks.Open(xlFile)
    End If
Next xlFile
End Sub

Open in new window

5 Comments

Hi guys,

I had this question after viewing Issue in VBA "getElementById".

My question is very similar. When I run the following code, sometimes it randomly works and inputs "Test" where the "Username" should be. However, 90% of the time, it gives the Run-time error '91': Object variable or With block variable not set. I don't understand why... I have also attached a screen shot of the inspected element. The url I'm visiting is :

https://app.impact.com

I've been able to determine that the issue happens at the following location:

Set HTMLInput = IEdoc.getElementById(elementId)

When the above code is run, HTMLInput = Nothing for some reason.

However, when I put a breakpoint at the error point, then run the code and I press F8 to step into the code once it hits the breakpoint, then the code seems to work as it should and the value "TEST" is typed into the Username box.

Please see the code below.. why am I getting this error and how do I fix it within the code so that it runs without the breakpoint and stepping into it?:

Private Sub pullData_Click()
    
    'Declare the variable for Internet Explorer
    Dim IE As SHDocVw.InternetExplorer
    Set IE = New InternetExplorer
    Dim myURL As String
    IE.Visible = True
    
    'Impact Radius URL
    myURL = "https://app.impact.com"
    
    'set the username variable to be put into the website
    Dim

Open in new window

…

2 Comments

Question by

Asatoma Sadgamaya

3d agoPrivate

Access VBA to name excel sheet

Hi

Please see below code, which transfers MS Access query into MS Excel.

DoCmd.TransferSpreadsheet acExport, 10, "QryInt", "C:\abcd\abcd.xlsx", True, "2017abcd"

Everyting is fine but sheet name starts with an underscore. ie. "_2017abcd". How can I name the sheet name just "abcd"?

Thank you
A

8 Comments

Starting with Angular 5

LVL 12

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

Hi all

I'm trying to find the last row number that has data in a specific range. For example what is the last row with data between A5:A20?

6 Comments

I have a .Net developer creating a DLL that we are going to Reference in an Access VBA application. However, whilst we can see the DLL methods (once referenced), he is struggling with a couple of aspects such as passing variables through and how best the code the .DLL so that Intellisense works as expected.

Whenever I have worked with external references in the past, I usually access them like this (rough example):

Dim obj1 As New TSDialler.Dialer
obj1.SignalKey = "ABC123456"
obj1.TelephoneDestination = "0123456789"
obj1.InternalID = "ZYX987"
If obj1.MakeCall = 1 Then
    MsgBox "Call made"
Else
    MsgBox "Call not made.  Error: " & obj1.ReturnMessage
End If

Open in new window

However, he has never worked in VBA and is struggling with the various aspects. His list of questions is below, but I was wondering if anyone had any links/suggestions for him to look at (more than just answering his questions).

Which kind of methods works?
Do properties work?
Constructors work, what about parameters?
How to do event pattern for a custom class and not UI control?
What pieces (that we use regularly) in C# that does not work with VBA when referenced?
Any article/doc that can be referred that has details for questions like above?

Any help would be greatly appreciated - sorry it's so vague.

7 Comments

access 2010
excel 2010

What I need: vba routine in Access
From Access i need to clear certain CELL range(s) in an Excel workbook and a specific sheet.
Workbook name "MyWorkbook.xlsm"
Worksheet "Import_Export_Data"

I need to clear certain CELLS on worksheet.
A4 TO A300
B4 TO B300
E4 TO E300
G4 TO G300

AND KEEP THE PRESENT formatting in the cells after clearing
Thanks
fordraiders

2 Comments

A have an Access form and have connected it to an sql server table using ADODB by setting the form's recordset property to the ADO recordset. The first record comes up fine, but when I navigate to the next record, my ADO recordset property goes to a state of 0. While the form displays the next record in the set, the form's OnCurrent event won't fire.
What is closing my recordset? I'm not doing any requering or anything?

10 Comments

Need help with getting text box (txtJobNo) to state Job Ticket No. once a row is selected in either Subform A or Subform B.
I've tried the following but it doesn't display any information.

Is there another event I need to try or do I have the wrong syntax?
Please note provided sample.

Private Sub txtSyncSubforms_AfterUpdate()

  Dim lID As Long
  lID = Forms![fMainMenuTimeSheetMonthly]![sfTimeSheetMonthlyDescOnly].Form!("cboJobTicket").Column(0)
  txtJobNo = "Job Ticket No: " & lID
  
End Sub

Open in new window

EE-sample.accdb

2 Comments

I am working with a .Net developer to create a DLL that I reference from my MS Access application. To help testing, I created a very basic Access 2016 db with a couple of forms and a module that will contain all of the code. However, when I send him the .accdb file, the startup form does not load. Plus, he can only go into Layout and Design view of the forms. The odd thing is that he can open the module and run the test functions just fine (either from the Immediate window or directly).

He doesn't get any security warnings, it's pretty much the same version (slightly different revision), our security settings seem similar (we have compared Trust-center settings and added his location) - but nothing - the forms will not open normally. Also, there are no compile errors.

I'm sure it's something to do with security - I'm just not able to see the fix at the moment.

Any ideas would be very much appreciated.

9 Comments

Question by

ArisaAnsar

5d agoSolved

Count of Rows using VBA

Hello.
I have a simple macro where I just need to count the number of rows on two sheets and list it on the Receipts tab.

I have the following code but I cannot seem to get it to work. Any help in identifying where I am going wrong will be appreciated.

Option Explicit
Dim wk As Workbook
Dim ws As Worksheet
Dim Lrow As Long
Dim rng As Range
Dim i As Long

Sub Env()

Set wk = ThisWorkbook
Set ws = wk.Worksheets("Receipts")

Lrow = .Range("A" & .Rows.Count).End(xlUp).Row

With ws
.Range("B2").Formula = "=CountA('0-29'!A2&Lrow)"
.Range("B3").Formula = "=CountA('30-60'!A2&Lrow)"
End With

MsgBox Completed

End Sub

6 Comments

I am trying to export a query to Excel using the following code:

Sub ExcelExport()
Dim newFile As String
newFile = Application.CurrentProject.path & "\export.xlsx"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryExport", newFile, True

End Sub

Open in new window

The problem is, that the file seems to be corrupted.

Am I missing something?

Thanks

Massimo
Mutterschaftszeitfenster.accdb

4 Comments

I am looking for a way to to kill a running exe from task manager.

3 Comments

Become a CompTIA Certified Healthcare IT Tech

LVL 12

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

LVL 6

Question by

Flora

5d agoSolved

How to move files from sub folders to a single folder?

7 Comments

LVL 1

Question by

John Carney

5d agoSolved

Sorting a range with 4 keys using VBA

Is it possible to specify 4 keys for a sort using VBA? Here's an example of what I need, but of course it throws an error "key4"

SortRng.Sort key1:=[A3], Order1:=xlAscending, _
             key2:=[B3], Order2:=xlAscending, _
             key3:=[C3], Order3:=xlAscending, _
             key4:=[L3], Order4:=xlAscending

Open in new window

Is there any way to do this?

Thanks,
John

3 Comments

I've been taking an Advanced Timesheet Sample online created by Helen Feddema and trying to advance it to allow for an entire months' worth of time entered.

My problem is where I need MS Access to freeze pane (similar in Excel) function the first three columns as you scroll to the right going further in the month. So my idea was to create a main form with two subforms (A,B) that would cooperate as each row is touched and entered and would use the same temp table: tempID to coordinate location.

Question 1: (How to get key down access the other subform and then come back to original subform?)

Explanation:
I got the coding (key down) to go up within rows on a Continous (not datasheet) form - but not sure how I can get the cursor resting at that particular row in the second subform (B) go to that location AND then come back to its original position in subform A? If I have to do it for each cell - just let me please have one example and I'll do the rest for the trouble.

Question 2: (How to tab from one subform to another?)
Same thing needed when I tab from column 3 in subform A - has to go to column 1 same row in subform B... I assume it will be the same coding except we will be looking for the TAB key press....

Question 3:
Finally as I scroll down horizontally and pick a new row - it will go also down to that row in subform B and then back to where you are...

Then it all has to work vice versa. So I'll take what you can give me and make that work on …

12 Comments

checking syntax from sql server 2008 to vba Access query to do the same thing

I have this sql server 2008 sql:
Update ro set [Realign_Using_Trackcode] in ('XXXXX')
from [ss_program_workflow].[dbo].[t_nsc_trackcode_reassign_overrides] ro
where [Realign_Using_Trackcode] in ('ZZZZZ')

then i have this access query statement in sql:
UPDATE dbo_t_nsc_trackcode_reassign_overrides SET dbo_t_nsc_trackcode_reassign_overrides.Realign_Using_Trackcode = "ZZZZZ"
WHERE (((dbo_t_nsc_trackcode_reassign_overrides.Realign_Using_Trackcode)="xxxxx"));

are they doing the same thing ?

Thanks
fordraiders

1 Comment

I am totally new to learning vba and access. If someone could teach me that would be appreciated. I have a command button and text boxes. Basically, I want to enter a value in the text box, and populates the rest of the text boxes related to that value.

3 Comments

I have a little block of code that generates a date value based on three parameters. I need to use it in two places.
1.      SET a temporary variable in a stored procedure to the output of the block of code
2.      Assign it to a variable in a VBA function behind a MS Access form
I want to have only one version of the code. Either a SQL Server scalar function or a SQL Server stored procedure. I don’t care which.

Here is where I stand in trying to achieve the goal.
SET temporary variable in a stored procedure
Declare
@RecurFreq int = 12      -- Periods per year
, @RecurYr int = 2020      -- Year
, @RecurSeq int = 2            -- Period
Declare @NewRecurDate date
-- Using this function works OK
      SET @NewRecurDate = dbo.fnGetPeriodEndDate (@RecurFreq, @RecurYr, @RecurSeq)
      SELECT @NewRecurDate as FromFunction

--      Executing this stored procedure works OK
EXEC dbo.rwGetPeriodEndDate @RecurFreq, @RecurYr, @RecurSeq
--      But I have not found a way to SET the @NewRecurDate variable to the result of the stored procedure execution

If that’s not possible, then I can go the other way. How do I call SQL Server from my VBA code to execute my fnGetPeriodEndDate function?
Here’s the VBA code:
Dim NewPeriodEndDate As Date
‘This works fine VBA knows how to deal with assigning the result of a stored procedure to a variable.
NewPeriodEndDate = ExecuteScalar("dbo.rwGetPeriodEndDate @RecurFreq = " & Me.grpRecurFreqSel & ", @RecurYr = " & Me.RecurYearSel & ", @RecurSeq = " & RecurSeq)
…

6 Comments

VBA

Related Topics

VBA

Related Topics