Modules & VBA :: Subscript Out Of Range If Run Twice
Sep 4, 2014
The below code exports a table (via a function) to a spreadsheet and saves it in a defined location. the code then opens the file does some work with it then moves it(left the move bit out as it works fine)
so when i run the code it works absolutle fine, table is exported, work is done and file is moved. however if i run the code again it fails, i get the error message out of range. i was originally getting this error when using the .usedrange.copy so i commented this out and now i get it on the next line that tries to work with the file so obviously something is wrong in my logic.
Code:
Private Sub Export2JDE_Click()
Application.Run ("JDE_Export") 'Exports to an xlsx file in the location described in the function.
Dim xlApp As excel.Application
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Dim xlApp As New Excel.Application Dim xlwrkBk As Excel.Workbook Dim xlSheet As Excel.Worksheet Dim strXLS As String strXLS = "c:AccessMyfile.xls" Set xlwrkBk = xlApp.Workbooks.Open(strXLS)
[code]...
The program is stopping at " Set xlSheet = xlwrkBk.Worksheets("singles")"
and I am receiving the following error box:
Runtime error 9
Subscript out of range.
I don't understand this error because I am using the same program code with another Excel file in another module and it works perfectly.
I am having trouble checking if the last array value is equal to 'D'.
This is what i have so far but it keeps saying that 'subscript is out of range'... 'g' by the way is equal to 1. It just really means that there's only one value in ArrAC.
I'm attempting to upload an excel file into access table using the wizard however I get this error message "subscript out of range" Please Help! I've uploaded other files via excel and don't have this problem.
Ok I have the following code which keeps producing an Error # 9 : subscript out of range.....
Public Sub SaveLineItems() On Error GoTo SaveLineItem_Error MsgBox "I am doing the line items" Dim sSQL As String Dim iLine As Integer Dim iMaxLines As Integer Dim iMonthCount As Integer Dim iFieldCount As Integer Dim sThisField As String Dim sFieldPrefix As String Dim aFields, aMonths, sInDirectCostId, sFY, sUser sFY = [Forms]![SWITCHBOARD]![cboFY] sUser = [Forms]![SWITCHBOARD]![txtUser] sInDirectCostId = sFY & sUser aFields = Array("cboDesc", "txt", "txtMemo") aMonths = Array("OCT", "NOV", "DEC", "JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP") iMonthLoop = 0 iMonthCount = 11 iMaxLines = 16 iLine = 1 Do While iLine <= iMaxLines iMonthLoop = 0 If Me.Controls(aFields(0) & iLine) <> "" And Me.Controls(aFields(0) & iLine).Locked = True Then If Me.Controls(aFields(0) & iLine).Column(2) = -1 And Me.Controls(aFields(3) & iLine) = "" Then MsgBox "You must have a memo for the program : " & Me.Controls(aFields(0) & iLine), vbOKOnly, "Missing Information" Me.Controls(aFields(3) & iLine).SetFocus Exit Sub Else sSQL = "UPDATE BUDGET_INDIRECTCOSTS_R_LINEDETAILS SET " & _ "ProgramId = " & Me.Controls(aFields(0) & iLine) & ", " & _ "Memo = " & Me.Controls(aFields(2) & iLine) & ", " Do While iMonthLoop <= iMonthCount sSQL = sSQL & aMonths(iMonthLoop) & " = " & Me.Controls(aFields(1) & aMonths(iMonthLoop) & iLine) If iMonthLoop < iMonthCount Then sSQL = sSQL & ", " End If iMonthLoop = iMonthLoop + 1 Loop sSQL = sSQL & " WHERE INDIRECTCOSTID = " & sInDirectCostId & " AND ID " = iLine End If ElseIf Me.Controls(aFields(0) & iLine) <> "" Then If Me.Controls(aFields(0) & iLine).Column(2) = -1 And Me.Controls(aFields(3) & iLine) = "" Then MsgBox "You must have a memo for the program : " & Me.Controls(aFields(0) & iLine), vbOKOnly, "Missing Information" Me.Controls(aFields(3) & iLine).SetFocus Exit Sub Else sSQL = "INSERT INTO BUDGET_INDIRECTCOSTS_R_LINEDETAILS " & _ "(Id, ProgramId, Memo" Do While iMonthLoop <= iMonthCount sSQL = sSQL & ", " & aMonths(iMonthLoop) If iMonthLoop = iMonthCount Then sSQL = sSQL & ") VALUES (" End If iMonthLoop = iMonthLoop + 1 Loop iMonthLoop = 0 sSQL = sSQL & "" & iLine & ",'" & Me.Controls(aFields(0) & iLine) & "','" & Me.Controls(aFields(2) & iLine) & "'" Do While iMonthLoop <= iMonthCount sSQL = sSQL & ", " & Me.Controls(aFields(1) & aMonths(iMonthLoop) & iLine) If iMonthLoop = iMonthCount Then sSQL = sSQL & ")" End If iMonthLoop = iMonthLoop + 1 Loop End If End If MsgBox sSQL If Len(Trim(sSQL)) > 0 Then Set db = CurrentDb db.Execute sSQL End If iLine = iLine + 1 Loop SaveLineItem_Error: If Err.Number <> 0 Then MsgBox "Line Item Save Error : " & Err.Number & vbCrLf & Err.Description End If End Sub
I have gone through and commented everything out and brought back only parts and here is what I have when I get the error the first time....
Public Sub SaveLineItems() On Error GoTo SaveLineItem_Error MsgBox "I am doing the line items" Dim sSQL As String Dim iLine As Integer Dim iMaxLines As Integer Dim iMonthCount As Integer Dim iFieldCount As Integer Dim sThisField As String Dim sFieldPrefix As String Dim aFields, aMonths, sInDirectCostId, sFY, sUser sFY = [Forms]![SWITCHBOARD]![cboFY] sUser = [Forms]![SWITCHBOARD]![txtUser] sInDirectCostId = sFY & sUser aFields = Array("cboDesc", "txt", "txtMemo") aMonths = Array("OCT", "NOV", "DEC", "JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP") iMonthLoop = 0 iMonthCount = 11 iMaxLines = 16 iLine = 1 Do While iLine <= iMaxLines iMonthLoop = 0 If Me.Controls(aFields(0) & iLine) <> "" And Me.Controls(aFields(0) & iLine).Locked = True Then If Me.Controls(aFields(0) & iLine).Column(2) = -1 And Me.Controls(aFields(3) & iLine) = "" Then MsgBox "You must have a memo for the program : " & Me.Controls(aFields(0) & iLine), vbOKOnly, "Missing Information" End If ElseIf Me.Controls(aFields(0) & iLine) <> "" Then If Me.Controls(aFields(0) & iLine).Column(2) = -1 And Me.Controls(aFields(3) & iLine) = "" Then MsgBox "You must have a memo for the program : " & Me.Controls(aFields(0) & iLine), vbOKOnly, "Missing Information" End If End If iLine = iLine + 1 Loop SaveLineItem_Error: If Err.Number <> 0 Then MsgBox "Line Item Save Error : " & Err.Number & vbCrLf & Err.Description End If End Sub
I have gone and researched the problem and have not been able to find anything that relates to this......
When I try to import a sheet from Excel into an existing table I get a subscript out of range error.When I then import the sheet into a new table and then import it into the existing table I was trying above immediately afterwards it works fin.
I am trying to import data from Excel 2007 to Access 2007. However, after hitting the final button to submit the import I get this message: "Subscript out of range."
I have tracked down the fields that seem to be causing the problem. Below is a picture of the Design View for my table. The fields named Ht, College, Dob, State, and Country are the fields that give me the subscript message (I know this because I tried uploading different times with just one of these fields mixed in with the fields that would upload). My first guess for the Height was just a matter of the data type being wrong, but I changed the Ht column to text on my Excel spreadsheet and the same message occurs.
College: Is listed in the picture. This is being pulled from another table. I have another field labeled Transfer College that does this as well, but the expression is a bit different, and any data in a Transfer College column on excel will import over. I have tried altering the College data type to match the Transfer college, but I have to delete too many relationships and it screws up the record source for all of my forms and reports.
DoB: It has an input mask of: 99/99/0000;0;" " Not sure why this doesn't work. Access seems to be picky with dates. When going through the import steps it changes my dates in my spreadsheet to a 5 digit number.
State: Again, another combo box with a value list. State abbreviations are the record source. I would think this would upload. Should I just remove the combo box and value list for now, and then reset this after I transfer my data over?
Country: Another combo box, but this one is pulling its data from a table with countries listed.
I am trying to generate a report from a select query using the report wizard. The wizard is showing the error of subscript of out range. What is the reason and how can be rectified?
Trying to import data from Excel into an existing table in Access 2007. The import fails and keeps getting the error message: "subscript out of range". All of the column headings in the 1st row are exactly the same as the table in Access, and the data types are all text except for the key which is a number. I've imported to this table many times and not had a problem and can't figure this out.
Is there any way of entering a mixture of subscript, superscript and normal characters in Access. I am trying to enter subscript numbers into a database of mineralogical formulae.
I am trying to simply find the sum of a range of data...I have tried putting the range into a the formula, and also creating aliases for the cells but it either doesn't work or I keep getting a name error in excel and I just can't seem to get the right code:
The range I am trying to sum is: Range(Range("B2"), Range("B2").End(xlDown)).Select
And I can find the cell that I want the sum to go into by: Range("B2").End(xlDown).Offset(1, 0).Select
As I said I have tried a few things but just get this right. Should I be using Value, Formula or FormulaR1C1?
Is there a font that comes installed by default in access(windows) that supports subscript? My subscript in access is showing up as a box. If I change it to a font that I have installed/purchased seperate from windows it gets displayed properly. The problem with this, is when it pulls this info displayed in that font to the website, it reverts it to a font that the user doesn't have installed and goes back to a box.
So need a default windows xp font installed in access/windows that supports subscript....
I'm trying to create some code that will create a new folder depending on the ID that is currently being added to the database.What happens at the moment is a new ID is generated which in turn is job reference.When this is added to the database a folder is created with a prefix of this ID number and a 20char test specified in a text field by the user.
What I am trying to achieve is this:If the ID = 57...Then a folder is create called 1 - 500 (and ignore if one has already been created, which it should have at ID number 1)...When the ID Number 501 comes along another new folder is create called 501 - 1000 etc etc.The idea is not to have 3000+ folders in just one folder making it look a bit messy and lengthy to look through.
I am currently trying to do a range join query in access (not an expert at all with it) but I realized that it's not easy. It probably needs a VBA code but still not sure how to do it. I basically have a column with number of days e.g
Number of days 18 1 0 56 8 -19 369
Eventually I would like to do a range join query (similar to excel range vlookup) with the following table
1. I have a sample database table in excel where depending on column 2 value >1 is checked and logical results by IF check placed in Column D. The values having "Y" in column D are placed in Columns E:F.
2. Now I want to place count of values meeting the condition on last cell of the filled E:F range.
3. VBA code written by me proceeds fine and places values meeting condition in column E:F
4. I am beginner to VBA and finding difficulty in proper code for placing sum of counts of acceptable values in range F2:F7 in cell F8 ie last value in the array. Presently I have placed the value by In-built Count function in Excel.
File Count on Macro from another table is attached.
I am having problems with Dlookup from a table which is linked to Excel.The heading on the fields from Excel have been imported as F1, F2, F3 etc... the code I am using is as follows .....
I need to create a very simple database that would just store records and produce a couple of reports. I have three tables: one with the roster , one has records of the inventory items people from the roster receive and another one contains types of inventory we have. Everything is very simple except for one part. Every time we make a record of an inventory item given to someone, it requires not only employee id and inventory type from the existing tables. It needs us to enter a serial number of an item. This serial number contains a letter and a number.
Looks something like this - M100. Many people receive a consecutive set of inventory items. For example, from M100 to M150. There is no way to have a separate table with all serial numbers because they constantly change. That is why we need to have two text boxes that would allow us to input a range of serial numbers or just one number. Then the program should separate numbers from letters, evaluate the range, create new records of numbers and then put new numbers and a letter back together into one field in the table where we have all inventory records.
I found the following code online that allows me to find numbers within a range, but it only works for numbers.
Dim varRange As Variant Dim lngLow As Long Dim lngHigh As Long Dim lngCounter As Long DoCmd.Hourglass True
[code]...
I then found a piece of code that is supposed to separate numbers from letters, but I can't find a way to make it work.
Public Function FindNum(strName As String) As String Dim strTemp As String Dim i As Integer For i = 1 To Len(strName) strTemp = Mid(strName, i, 1) If (Asc(strTemp) < 91 And Asc(strTemp) > 64) Or (Asc(strTemp) < 128 And Asc(strTemp) > 96) Then FindNum = Right$(strName, Len(strName) - i) End If Next i End Function
how to alter the code to make it work for my specific situation.
if it is possible to store text in a table that includes subscript / superscript characters. As an example; need to indicate the units of measurement for some data and therefore need to be able to pull data from the table such as the following: kg/m2, m/s2 etc. In both these case i need the 2 character to be superscript.
I'm trying to make this an automated process so pulling it directly from the database.o a method of storing the data as a text string would be ideal.Otherwise i imagine a rather difficult VBA function will be involved
I want to select a date range from "Production" table where it agrees to the Dept_ID too. And then calculate the summation of the columns "hours", "produced" & "waste" of that particular range selection.
This is my code:
Code: Option Compare Database Private Sub cmdCal_Click() Dim sql As String Dim rs As Recordset Dim qdef As DAO.QueryDef Dim hours, waste, produced As Integer
[code]....
But it returns nothing, When i remove the errorHandler, it says that no records were found.
I've adapted some code I found which works and allows me to import data from my access table "Device Text" to a specific range in an existing excel worksheet by copying a field "TextEdit" in the recordset. the code is placed in a module behind a command button on an excel worksheet. I have used import to excel because this seems to be less complicated for my needs than exporting from access.
Everything works fine if a single range is set , however I would like to set the range(rng) criteria in the code below to start at a certain cell depending on the value in another field in my access table called "LoopID" this is a number field which is not unique and can be 1,2,3 or 4. I know it wont be evaluated but I've entered the gist in red in my code.
I don't need to import it but I suspect I have to bring LoopID into the recordset somehow to use it,
Code: 'DIM STATEMENTS Dim strMyPath As String, strDBName As String, strDB As String, strSQL As String Dim i As Long, n As Long, lFieldCount As Long Dim rng As Range 'instantiate an ADO object using Dim with the New keyword:
I am trying to update the range for the graph automatically and I'm calling this from Access because my data exists in Access. I've attached a sample file.what I am trying to do:
1) click on the button 2) button will update the usedrange in "trending" tab 3) have the graph's data range based on the usedrange.
Code:
Dim xlApp As Object Set xlApp = CreateObject("Excel.Application") xlApp.Visible = True xlApp.Workbooks.Open "C:Documents and SettingsgjiaDesktopmetric_DEV.xlsx", True, False
[code]...
note: the columns will grow bigger to the right, which is dynamic, so I've used usedrange method, but the code fails on the usedrange line saying the object or method is not being supported...
I have a multiselect listbox and two date fields (StartDate & EndDate) in an Access form.I am trying to add records to the Table through the form on a button click.I select multiple items from the list box and the date range between the start date and end date will be equal to the items selected from listbox.For each item selected from the list box I need to add a separate record with a date.So the first record will have List box item selected1 and the start date.Next record will have item 2 from list box and date as dateadd("d",startdate,1)And final record will have last item selected from the listbox and date as enddate.
I am trying to use VBA to create a filter by date range. the user inputs 2 dates and the database filters all records by dates in between the 2 dates,
Code below
Dim var_CustDate1 As String Dim var_CustDate2 As String var_CustDate1 = InputBox("Please enter start date in format DD/MM/YYYY", "Enter Date", Date) If Not IsDate(var_CustDate1) Then MsgBox ("not a valid Date")
[Code] ....
I've tried every combination of format for the dates but this is the closest ive got it to work,
if i enter dates 01/09/2013 and 12/09/2013 the filter works for the days in the month but also displays previous years, but if i change the dates to 01/09/2013 and 13/09/2013 it starts displaying all dates for all years in the months September, October, November and December.
