Modules & VBA :: Using Access Of Count Worksheets Loop
Mar 10, 2015
I would like Access to open an Excel file count the worksheets and then give each tab the name of the column heading in cell A1 of each worksheet. I had no problem with the code in Excel but when I tried to add it to Access I am getting an error on the WS_Count = obj.ActiveWorkbook.Worksheets.Count line. The error message is "Object variable or With block variable not set".
Here is the code:
Code:
'This block of code will cycle through each tab and name them
Dim WS_Count As Integer
Dim I As Integer
[Code]....
View Replies
ADVERTISEMENT
Sep 30, 2013
I'm exporting some data from an Access Database into an Excel Spreadsheet to create a report in Excel, and I'm running into some problems with that.
The idea is I have an Excel Template with a tab to take raw data from an Access Query, and then a couple tabs that manipulate that data and a final tab to format it for reporting. Basically, the formatted tab uses lookups to find data that's been exported from the query and display them in appropriate columns and rows (producing a result sort of like a cross-tab query but where I've got better control over the presentation of the results). My end-goal is to have this data formatted, to replace all the lookups with hard-coded values data, and then to remove the raw data tab and other tabs with lookup data to have a clean report ready for printing.
It's that last step - deleting all the extraneous tabs that are no longer feeding any equations because I've copy/pasted values over the lookup equations - that's proving vexing. Everything through this point is in the same On_Click event code:
Code:
Private Sub Command0_Click()
'Note: I'm using early binding right now to take advantage of Intellisense;
'My plan is to go back and replace this with late binding once I have code that works.
Dim objXLApp as Excel.Application
Dim objXLbook as Excel.Workbook
Dim objXLWS as Excel.Worksheet
Dim strPath as String
[code]....
I don't get any error messages. I actually have a messagebox thrown up in the code to inform me that I'm getting to the right worksheets selected. (The loop is selecting the worksheets as expected. But when I open the file in Excel... all of the worksheets are all still there, including the ones that should've been deleted.Is there something I should be doing to make sure the worksheet deletion goes through?
View 2 Replies
View Related
May 13, 2015
I am trying to loop through a table and count the duplicate records only once. Please see the attached file.
1) In the test table, I want to loop through all pt_accts.
2) In the countable column, if an acct repeats, I essentially just want to put "1" in one of the records. For the non-repeating accts, putting in a 1 is sufficient. Basically, I just don't want to count the duplicate ones multiple times.
In the example, acct "aaa" repeats, but there is just one "1" in the countable column. "eee" repeats as well, but there is just one "1" and the rest is 0.
Is this possible via VBA?
View 14 Replies
View Related
Jul 2, 2013
I have a code that allows the user to select the file that they want to import, however it automatically imports the first worksheet into a specific table. Is it possible to change the code to make it import the first worksheet (or with a specific name) into one table and another worksheet into another table?
Code:
Dim dlg As FileDialog
Set dlg = Application.FileDialog(msoFileDialogFilePicker)
With dlg
.Title = "Select the Excel file to import"
.AllowMultiSelect = False
.Filters.Clear
[Code] .....
View 1 Replies
View Related
Mar 9, 2014
I have 192 databases that I need to redact certain information in certain columns. Generally, I'm looking for field names like name, first name, last name, address, address2, shipping address, mailing address, and phone numbers. Not sure how to get this information without going into every database and every local table inside the databases. Is there a way to get this information programmatically and just access the databases and table that I need to redact info in?
View 6 Replies
View Related
Aug 16, 2013
Using Access 2010: I have a query with four fields: ORG_NAME, PERS_NAME_LAST, CountOfORG_NAME, and BdMbrCount. There are a couple hundred companies in the database with 1-7 people associated with each company. I need to number each person so that they have a number, 1-7 in the MemberCount field of my query.
I have my query connected to VBA code.
I have experimented with code that I have found on the forum, just to see if I could get something to work and I am getting “Undefined function ‘BdMbrCount’ in expression. I am trying to pass [ORG_NAME],[PERS_NAME_LAST] to my function and assign the value of BdMbrCount to a new field in my query, BdMbrCount.
Code:
Function BdMbrNumber()
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("YourBdMbrsRRecognizedQry")
'Set rst = CurrentDb.OpenRecordset("SELECT [ORG_Name],[PERS_NAME_LAST],[CountofORG_NAME], [BdMbrCount] FROM YourBdMbrsRRecognizedQry")
Dim ORG_NAME As String
[code]....
View 14 Replies
View Related
Mar 12, 2014
What I want to do instead is open an existing .XLSM wokrbook delete or update the 7 sheets it creates and replace them with the new query results from access.
I love this code below because it works really well but now I have a new requirement. I have a workbook that has a "dashboard" sheet that looks at the sheets from acccess and summerizes the data. So, I'd like Access to open that "template" excel workbook and delete the old sheets and put in the new ones..The required sheets to keep are called "Metrics", "Validation" and "Mara"
What I was trying to do for the past few hours was another work around which was to have Access run this code, then excel run some code to import the "dashboard" formulas but I can't get it to copy to another workbook because it links to the OLD workbook..Here is the working code that needs modding:
Code:
Option Compare Database
Public Function ExportAdvanced()
Dim strWorksheet As String
Dim strWorkSheetPath As String
Dim appExcel As Excel.Application
Dim sht As Excel.Worksheet
Dim wkb As Excel.Workbook
Dim Rng As Excel.Range
Dim strTable As String
Dim strRange As String
Dim strSaveName As String
Dim strPrompt As String
Dim strTitle As String
Dim strDefault As String
[code]...
View 3 Replies
View Related
Aug 23, 2013
I am trying to improve my code by making it more readible. The following code works, but it is certainly not the most efficient way. I`m trying to write a loop to make certain elements in an Access report visible/invisible, but I can`t address the visibility property of these items while iterating over i.
Code:
DoCmd.OpenReport "tblInterval subreport", acViewDesign, , , acHidden
Reports![tblInterval subreport]!BoxInsp1.Visible = False
Reports![tblInterval subreport]!BoxInsp2.Visible = False
Reports![tblInterval subreport]!BoxInsp3.Visible = False
[code]...
View 2 Replies
View Related
Nov 24, 2013
I opened a 2007 Access db in Access 2010. This DB worked perfectly in Access 2007, but when I opened it in 2010 my recordset.recordcount no longer works.
This is what I have:
rsGetInst was previously defined
Dim intInstCount as integer
Dim rsGetInst As New ADODB.Recordset
rsGetInst.CursorLocation = adUseClient
rsGetInst.CursorType = adOpenDynamic
rsGetInst.LockType = adLockOptimistic
rsGetInst.open "Select * from tblInstruction where CustID = " & intCustomerID
intInstCount = rsGetInst.recordcount
At this point a get a "type Mismatch" error, and it is happening in all my recordsets recordcount.
Is there a command or reference that needs to be change when using Access 2010. When I compared the references the only difference is that in 2007 we reference Microsoft Access 12.0 Object Library and in 2010 is Microsoft Access 14.0 Object Library.
View 1 Replies
View Related
May 30, 2014
I have a query where I need to incrementally count the repetition of column values and add that value in a new column.
Example:
"Column One" and "RepeatCounter" are fields in a query.
Column OneRepeat Counter
Value 11
Value 21
Value 31
Value 12
Value 13
Value 32
I want to create the outcome in the field "Counter" in an Access Query. I am a novice in Access VBA. I found I can do it in Excel with the formula CountIF. I am unable to do it in Access.
View 9 Replies
View Related
Oct 10, 2006
OK.. any suggestions as to how I get my Excel worksheets imported in Access? I have tried and tried.. and it gives me a message saying that it was not imported..
I am new at access..
any help would be appreciated!
View 4 Replies
View Related
Mar 11, 2014
I have to loop thru 2 recordsets.I need to first determine the quarter , then retrieve a value from each of the 6 fields for that quarter and compare that value against a previous years value that is also stored in the table for each quarter.the following is the field names not the field value.
Tier 1 2 3 4 5 6 PrYr
Qtr1 Q1T1, Q1T2, Q1T3,Q1T4, Q1T5, Q1T6 .385
Qtr2 Q2T1, Q2T2, Q2T3.Q2T4, Q2T5, Q2T6 1.25
Qtr3 Q3T1, Q3T2, Q3T3,Q3T4, Q3T5, Q3T6 .774
Qtr4 Q4T1, Q4T2, Q4T3,Q4T4, Q4T5, Q4T6 .333
if Qtr1 = Q1T1 = 0.44 Q1T2 = .50, Q1T3 = 1.45, Q1T4 = 3.00, Q1T5 = .25, Q1T6 = 6.0
So I need to be able to set the value of PrYR = .385 and compare against the value of the 1st qtr for each tier for Qtr1.Then go to Qtr2 and repeat the process but grap the Q2 PRYR value = 1.25 and compare against all Tiers for Qtr2.As so forth for each quarter.then I need to compare the value of the PrYr and if it is the following then
If PctYrlyIncrease< Tier1 Pct (Q1T1) Payout = 0
elseIf PctYrlyIncrease> Tier1 Pct (Q1T1) and < Q1T2 then
Sum(TotalNetUSExp * T1E)
ElseIf PctYrlyIncrease> Tier2 Pct (Q1T2) and < Q1T3 then
Sum( TotalNetUSExp * T2E)
and repeat for each Tier per Each Qtr.Here is my code so far:
Code:
Public Function BkOvrCalc(ByVal gContractID As String) As Long
Dim curDB As DAO.Database
Dim strSQL As String, strSQL1 As String
[code]...
View 3 Replies
View Related
May 11, 2014
Here is the code:
Private Sub Form_Load()
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("select * from tblpatient, dbOpenDynaset)
If Not (rs.BOF And rs.EOF) Then
rs.MoveFirst
Do While Not rs.EOF
' do stuff
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End If
End Sub
I have watched it step through on debug and it does everything right for the first record, but it seems to come back to the same record.
View 3 Replies
View Related
Feb 23, 2014
I have a form where a textbox has shows the NameNo from the NameNo column in a db. At the moment I am using a dlookup to set the textbox = to the NameNo, but obviously this is only returning the 1st value in the column. How to get this form to automatically display each NameNo, creating a pdf of the form and then moving on tto the next nameno. I have the code to make the pdf working so far, all I need now is to figure out how to use the textbox to loop through all the NameNo's in the table.
View 3 Replies
View Related
May 6, 2015
i have the following code to get file list from specified directory:
Code:
Dim db As DAO.Database
Dim TB1 As DAO.Recordset
Dim p As String, x As Variant
[Code].....
the field "File_Name " is not set to accept duplicate the above code work fine , but when it find the file name exist on the TB1 it return an error i need the code if find any error continue to retrive other files on the folder not exit loop and also i don't want to make check if file exist it will lower the speed of code
View 6 Replies
View Related
Jun 4, 2014
Is there a way of looping through a non update able continuous subform, and using information from each line.
Which creates a new record in another table? so if there are 3 records in the subform it creates 3 new records in the other table and so on?
View 3 Replies
View Related
Jun 30, 2015
why my access always go over the loop that I am declaring..Here is a sample of my code:
Code:
Dim rs As New ADODB.Recordset
rs.ActiveConnection = conn
rs.Open mySQL, conn, adOpenDynamic, adLockOptimistic
For lngProd = 1 To 5
[code]...
I get the error of access cannot find the tbl_data6. Where in the declaration I wrote 6??
View 1 Replies
View Related
Aug 26, 2014
I believe this is most recurring scenario for all. Any simple way like:
Code:
For each file in folder.files
Msgbox File.Name
Next File
For your information the above code doesn't work
View 10 Replies
View Related
May 15, 2014
Basically what I am trying to accomplish is uploading multiples file automatically into server. The code I am trying to use works great if the user wants to upload a single file manually because the code prompts you to choose the file and I am wondering if there is a way to tweak the code. here is the code I am using
Code:
Private Sub Form_Load()
Dim objFTP As FTP
Dim strfile As String
[Code].....
View 3 Replies
View Related
Nov 18, 2013
this is a progress bar, what i need is, while execute loop (progress bar) but also execute next command = "LedgerExe:
On Error GoTo Proc_Err
Dim inti As Integer
Dim dblPct As Double
[Code]....
View 1 Replies
View Related
Oct 3, 2014
I work for an insurance company where various (and multiple) discounts or loads can be applied to a quoted premium for one reason or another.
These discounts/loads are stored in a table with a corresponding customer ID, where each row represents an individual discount/load (labelled and ordered-by a 'Step').
Unfortunately, the resulting premium from these discounts/loads is not stored in the database and are calculated on the fly by the front-end.
I have a requirement however, to store the 'new' premiums based on the stored discounts/loads for a report.
My problem is that the calculations must occur incrementally one after another, where the discount/load at each 'Step' applies to the resulting premium from the previous calculation.
The attached spreadsheet is demonstrative of the existing table where two additional columns have been added to show you what I need to calculate. 'Price_Amount' represents the discount/load in monetry terms relative to the calculation and 'New_Prem' is the premium resulting from the calculation step.
Notice that each discount/load applies to the previously calculated premium rather than the original one (Original_Prem = the starting point from which all further calculations should apply).
The full table would include many more rows for different customer ID's, where the number of 'steps' could be as few as 1 or as many as 7 per customer ID (ordered by 'Step').
Having toiled for many hours in Access to achieve the above, I am now resigned to the fact that only some kind of VBA function will achieve my requirements.
This function should take the Original Premium for each CustomerID and loop through each 'Step' applying the relative discount/load based on the value in 'Pricing' and the number type in 'Price_Type'. Once the function has calculated each step per CustomerID and has reached the maximum 'Step', it should move on to the next CustomerID.
such can only articulate the requirements without being able to convert this into actual coded logic. Nonetheless, I am in a bit of a pickle with this one and am under some pressure to create the report.
View 7 Replies
View Related
Apr 30, 2015
Is it possible to run a loop through each record in query, but refresh the query in between loops?
The query I am looping needs to be updated before moving to the next record.
t can't figure out how to get the query to refresh before looping.
View 3 Replies
View Related
Aug 27, 2013
I am using to select and append data to an output table. Everything is working fine until the last step. The last step appends all the data to an output table from the initial table. The intermediate steps select the data to output.
"AddExpenseToOutput()" is stopping about halfway through the list of criteria. Initially I thought there might be a null in the list, but it doesn't look like there is after dropping everything into Excel to manipulate.
Code:
Option Explicit
Dim rstAccount As DAO.Recordset
Dim rstAccounts As DAO.Recordset
Dim CAccounts As String
Dim Item As String
Dim AccountKey As String
[Code] ....
View 4 Replies
View Related
Aug 18, 2015
I have a sample database (attached) where I have a form called frmVoteHeadsOne. The voteheads that I have are swimming (Swim) and transport (Trans), which both appear for data in a table. On the form, from the combo list, I would wish that if I select swimming, for example, only Mark Tyler's name is returned since he has paid 2000; if I select Transport from the same list, only John clement's name is returned.
My purpose for doing this is because in the actual database, I have 21 different voteheads which I would like to get different results from based on what the user selects from the combo list.
View 8 Replies
View Related
Aug 6, 2015
I've got a problem with a loop for producing worksheets (reports). When everything works there's no problem but when one of the reports namely rptChemoOralWorksheet doesn't have data available to produce the report the loop finishes and then all remaining worksheets don't print.I've put in a standard error handler with MsgBox, but it's never triggered. I've also tried
Code:
On Error Resume Next
but again rather than skip the DoCmd.OpenReport "rptChemoOralWorksheet" (when the error is present) and move to the next one to continue printing through the list the loop stops and the subsequent code starts to run.
Code:
Dim mydatabase As DAO.Database
Dim rst As DAO.Recordset
Set mydatabase = CurrentDb
Set rst = mydatabase.OpenRecordset("atblChemoWSLoop", dbOpenDynaset)
Dim rsBN As String
Dim rsDose As Double
[code]....
View 4 Replies
View Related
Jun 16, 2014
Im trying to create a record set that compares a quantity value in the recordset to a Value in a temporary table that holds the most recent remaining quantity. I have the following code in the after update of a text box, but it does not trigger. I want it to run after a value is entered into the text box. Is the code wrong or the location im putting it incorrect?
Code:
Private Sub txtQty_AfterUpdate()
Dim rs As DAO.Recordset
DoCmd.OpenQuery "qryQuantitySoFar"
Set rs = Forms!frmReceive!sfrmReceiveDetailEntry.Form.RecordsetClone
[Code] ....
View 11 Replies
View Related
