Modules & VBA :: Subscript Out Of Range In Excel Worksheet
Sep 10, 2013
I have written the following program code:
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.
View Replies
ADVERTISEMENT
Jan 11, 2015
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.
View 3 Replies
View Related
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
[Code] .....
View 8 Replies
View Related
Aug 6, 2013
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.
Code : If Not ArrAC(g) = "D" Then
View 2 Replies
View Related
Feb 17, 2015
I've lost two shapes in a worksheet, and can't find them because they are formatted with no fill and no borders.
There are a number of other shapes in the sheet which are formatted and so are visible.
How I can find my two runaway shapes? It's important because they have macros assigned, and so a user may find himself clicking on them unexpectedly, with unfortunate consequences.
View 2 Replies
View Related
Aug 11, 2015
I am new to MS Access and have been trying to use VBA to import a specific worksheet called "Access" in an Excel file (from about 400 users) into a single table. I want the code to search through one folder and import the "Access" worksheet in each Excel workbook within that folder. Each user has the same worksheet name. Here's my problem. I got this VBA code from: AccessMVP where KDSnell gave examples of how to import Excel worksheets into MS Access Tables.
Sub ImportExcel()
Dim strPathFile As String, strFile As String, strPath As String
Dim blnHasFieldNames As Boolean
Dim intWorksheets As Integer
' Replace 3 with the number of worksheets to be imported
' from each EXCEL file
[Code] ....
When I execute the code. Nothing happens. I go to the table and find nothing....or I get Runtime error 3011, where MS Access can not find the object "Access". Also, is it possible to update the table without duplicating records?
I'm using Excel 2010 and Access 2010.
View 5 Replies
View Related
May 18, 2015
I have a function which exports some data to Excel, performs some work, and then attaches the final report to an e-mail. As a result of various temporary columns being added / removed (for formulas to determine scope etc.), the main sheet ends up scrolled to the bottom of the used range. Before I attach it to the e-mail, I want to scroll the sheet back to the top-left corner (A1).
Code:
Public appExcel As Object ' Excel Application object (late-bound)
Dim objWorksheet As Object ' Excel Worksheet object (late-bound)
objWorksheet.Activate
With appExcel
With .ActiveWindow
.ScrollRow = 1
.ScrollColumn = 1
End With
End With
It worked up until this week, when I had to make some changes to the rest of the code to refine the output. Now, when it hits the ".ScrollRow = 1" line, I get RTE 91..how I can get the sheet to scroll to the top-left before I save it? (Such that, when subsequently attached to the e-mail, the recipient will open the workbook at the top-left cell and not sacrolled to the bottom).
View 8 Replies
View Related
Dec 5, 2007
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.
Thanks
Kon
View 1 Replies
View Related
Jul 24, 2006
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......
Thanks,
Richard
View 1 Replies
View Related
Jun 6, 2014
What the Variant value on Ms Access latest version is same with Ms Access previously(97,2003).?
I am using office 2010
I have face error again in line
Code:
-----------------------------------------
bd = ""
bd = Split(bx(1), " ")
For i = 0 To (UBound(bd) - 1)
Me("p" & i).Caption = bd(i)
Me("p" & i).Visible = True
[Code] .....
View 12 Replies
View Related
Aug 13, 2013
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.
View 1 Replies
View Related
Aug 14, 2013
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 fine.
View 3 Replies
View Related
Sep 16, 2010
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.
View 14 Replies
View Related
Sep 10, 2013
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?
View 4 Replies
View Related
Jul 17, 2014
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 .....
LOOK = DLookup("F6", "DATABASE_LOOKUP", "F1 = 1338087")
F6 = A date I am looking up.
DATABASE_LOOKUP = table I am looking up.
F1 = 1338087 = The record I am looking for.
View 5 Replies
View Related
Mar 18, 2015
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:
[Code] .....
View 5 Replies
View Related
Jul 12, 2005
Hello,
I need to pull data from specific worksheets in Excel from Access. I have code to get the data from a worksheet, but the sheets themselves have dynamic names that I will not know until runtime.
There is one worksheet called "Template" that is consistent. I will need data from every sheet after that one, regardless of its name.
So basically I need to find which sheet "Template" is, find all the worksheets after that one and get their names. Then I can use my existing code to loop through each existing sheet (by name) and pull the data I need.
I'm thinking this is somewhat simple--there has to be some command to find all the worksheet names, but I haven't found it yet.
Any help is appreciated!
View 5 Replies
View Related
Nov 6, 2014
I'm trying to make hyperlinks to specific pages of a workbook to excel. If I put the hyperlink of the form as follows then works properly: C: Users Desktop test.xlsx # Sheet2!A1 -> leads me to Sheet2 document "test".The big problem comes when the leaves have spaces in its name, that is, if the sheet is called Sheet 2:
C: Users Desktop test.xlsx # Sheet 2!A1 -> does not work.
C: Users Desktop test.xlsx # 'Sheet 2'!A1 -> does not work.
View 3 Replies
View Related
Nov 6, 2014
I'm trying to make hyperlinks to specific pages of a workbook to excel. If I put the hyperlink of the form as follows then works properly:
C: Users Desktop test.xlsx # Sheet2!A1 -> leads me to Sheet2 document "test".
The big problem comes when the leaves have spaces in its name, that is, if the sheet is called Sheet 2:
C: Users Desktop est.xlsx # Sheet 2!A1 -> does not work ...
C: Users Desktop test.xlsx # 'Sheet 2'!A1 -> does not work ....
The solution to rename the sheets is not possible ...how to solve this problem?
View 7 Replies
View Related
Mar 1, 2013
I am having an issue with an Access import. It worked correctly bringing in a sharepoint spreadsheet until a date field was added to the spreadsheet and the structure to the database. Now it drops any additional fields that are added. At first I thought if I added a filler field after the field I needed that it would still drop the last field but it dropped them both. If I insert the field in the middle of the spreadsheet....(where I really want it). It drops the last field of the email address. Which I need to send out notifications. I have tried designing a complete new table and importing it, but it continues to do the same thing.
Not sure if my problem is with the spreadsheet or Access. Somehow it is still looking at the old structure when I import even if I create a new table. Not sure what is happening.
View 1 Replies
View Related
Jun 6, 2014
I'm trying to subtotal data in an excel file but I am getting a subtotal method of Range class failed in the red text below. I have been trying to get this to work with no success.
Code:
Public Function SUMMARY()
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
With xlApp
Dim wb As Object
[Code] .....
View 3 Replies
View Related
Feb 28, 2014
I am trying to connect to excel from Access database. Once I make the connection I want to delete data from a range on a specific sheet. There are 4 columns on the spreadsheet but i wouldn't know how many rows. So for example, A150... but I may not know what the last row is. How would I be able to delete data from a range of columns to the last row?
View 4 Replies
View Related
Dec 19, 2005
Hey folks.
I have an Access db needing populated via a worksheet with unknown # rows and known # of columns. Before pulling this data in I must do a row count to determine how many records will be added to the db. Otherwise, it would be a very simple thing to just copy the contents of the worksheet over.
Known:
- Only using DAO.
- The access table is already created matching the column count in the worksheet.
- The spreadsheet will have an unknown row count.
- Some rows in the spreadsheet will have various cells empty.
- A completely empty row will mean EOF.
Need:
- I need to determine how many rows are in the spreadsheet before copying into db.
Problem that occurs from attempt:
- Overflow issue. I seem to be counting every single row on the worksheet, even though only 2 contain data.
Any ideas out there? Pasting below my latest attempt.
Dim strStorage as integer
strStorage = CLng(xlsWST.Rows.Count)
Thanks folks.
-Tethys
View 4 Replies
View Related
May 31, 2007
Hi there,
I'm pretty new to Access so I hope this question is not to simple/stupid ;)
Ok, I have an Excel Worksheet that I want to import into an Access Table. The column headers are identical! The only problem is:
- the Access table looks up a couple of the columns from other Access tables. When I try to import the Excel data, Access does (properly) report an error.
- Error: the records from the Excel file were added to the Access table but not all values were imported (no values were imported into the lookup columns...)
Any idea how I should procede so that I can import my data into Access?
Thanks!
Steve
View 3 Replies
View Related
Apr 23, 2013
I am using Access and Excel 2007. This Db is for a mental health practice to track and store the paper questionnaires that the patients fill out. The data then needs to be exported to Excel so that it can be imported into a proprietary software that analyzes the data and recommends treatment plans. (It does NOT play nice with Access, forget it.) There are many forms and all of them are fine and export to separate worksheets no problems.
Now for the problem child: One form has 493 fields. Obviously I could fit that into two tables, but it seemed cleaner to use a main form and main table with the patient information (ID_Number, Name, Date, etc.), then tabbed subforms and separate tables for each “section” of the questionnaire (School, Work, Home, etc. There are 11 tables/forms in all.) These tables are all related by the ID_Number. The problem is the export. I need all 493 fields to write to one worksheet in order. This would of course involve removing the ID_Number field from all the tables except the main one. A query obviously can’t handle that many fields. VBA I can TransferSpreadsheet but then each table goes to a separate worksheet.
I’m thinking maybe calling some SQL code that will drop the unneeded fields and keep appending the data to the worksheet one table at a time? Of course the rows would somehow need to be defined WHERE ID_Number = ID_Number so that the same patients information is all on one row.
View 10 Replies
View Related
Jul 20, 2015
I have an access 2010 database that looks after all the engine data for a fleet of engines. Each engine has a unique serial number and that serial number is displayed in a test box on a form using a search function.
Each engine serial number has a corresponding excel worksheet where the engine data is displayed as a graph.
Is there anyway that I can have a control button on the form that will allow me to display the excel spreadsheet corresponding to the engine serial number displayed in the text box?
View 14 Replies
View Related
