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 RepliesI 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.
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
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
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.
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.
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] .....
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] .....
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 RelatedI 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 RelatedI 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
So I have had Access for all of one day now. I am trying to import an excel file into access as a table. I successfully imported the first file but the second file gives me an error that says "An error occurred trying to import file ____. The file was not imported." What are the possible reasons this error has occurred? Both excel files I am importing are in the same format, so I am confused as to why one worked but the other did not. Thanks for any help.
View 2 Replies View RelatedI am trying to export from Access to Excel. I am relatively new to this process. I am receiving an error that I am not using a valid path. I have tried to recreate the former path without any luck.
What I had done was to delete some old information that was making the former Excel sheet too large and create a more up to date database. I had to reformat the spreadsheet and export the new information to excel but when I pull it in it will not pull into the shared network drive without giving me the error message.
I want a string of code that allows me to import a series of delimited text files into access using VBA. I have the first bit of code down:
Code:
DoCmd.TransferText acImportDelim, deltxtimptbl, "Delivery(local)", "msfs3109data1shareeveryoneprorep ranhistDelivery" & currentdate & ".txt"
The text files hold data for a specific days work. Each day has its own text file. I would like to be able to import the text files within a specific date range, specified by the user. Below is the total code i have for my form button:
rivate Sub Command0_Click()
Dim startdate As String
Dim enddate As String
Dim currentdatex
Dim count As Integer
count = 0
[Code] .....
Does anybody know what this error message refers to?
"Reserved Error (-1517); there is no message for this error."
It just started happening today, and I haven't even made any changes to the database. It occurs when I hit a button I have to run a macro.
the macro does the following:
1) Shows all records
2) Requery
3) ApplyFilter.
The Where Condition for the filter is:
Right([tblContracts].[JobNum],4)=Right([Forms]![FrmContProc].[txtFindJobNum],4)
The weird thing is that it only occurs if the Form window is taller than 1/2 of my viewable area. If the Form window is 1/2 the viewable area or shorter, it works OK. This was running fine earlier today, but about 4:00 pm (03/31/06) this started happening.
If anybody knows what this error means, or how to get rid of it (I really need to use this window in full-screen) then please let me know.
-Thanks, Sean
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.
Guys I don't know if any of you have done this. I need to import a formatted excel file into an access table, I know that I need to write a vba code to do it, can someone please give me a tip or an example. The excel file is a formatted form, it's not based on columns & rows!!!! Please Please assist.
View 1 Replies View RelatedHello,
I have a table that I would like to update with information from a spreadsheet. The table are all of the property records for the county I live in. The table has a unique data field for every record called a "PIN" (Property Indentifier Number). It is in this format: 12-3456-78-90. Because the PIN's were hand entered, sometimes there is a space between the dashes and the numbers, but each PIN is different for every record.
The data from the spreadsheet I want to import has updated sale information for the property records in my table. I want to associate the PIN's in the spreadsheet to the PIN's in my table and only import the new owners name, sale date, and sale price from the spreadsheet to the table.
How would I do this import? It is probably straight forward, but I am a newbie with Access.
I am concerned that because the PIN from the Speadsheet was typed differently from the one table, the import won't work. The numbers are the same, there are just spaces between some of the numbers and dashes.
Thanks
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] .....
There are some web forms where people can submit work requests to my team. The data is fed into Excel in .csv format. Each request adds a new row to the spreadsheet. Nothing ever drops off.
I am having problems getting that data into my "final" Access tables without repeating the data with each subsequent import/append. There is no unique identifier in the Excel file, only the raw data, all fields of which could be duplicate across requests.(requestor, phone, request by date...) In my Access tables, I have the same fields as in the Excel file, along with an AutoNumber primary key and some additional fields where we can enter when work began and was completed and to who it was assigned.
Say for example 5 requests come in and thus create rows 1-5 in the Excel file. I then import the data into a temp table, and then append to my final table. Works. But say an additional 2 requests come in...I then do another import and append and my final table ends up with rows 1-5 AND 1-7.
I was thinking composite key of some sort, but none of the fields lend themselves to that. Plus I need the ability to edit some of the imported data once it hits my final table (ex: changing a request-by date or request detail), so a field by field comparison of the import table to my final table to determine uniqueness probably won't work either.
I'm thinking the solution would be to add a unique identifer at the source level, but I'm pretty much left to come up with a solution in Access as the web team's resources are unavailable for a few months.
Any ideas to get me started???
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!
Hi all,
I am trying to import a sheet from excel into a new table in access. One of my columns has a date value which, when imported changes to the actual appliacation value (i.e. 1/1/2006 becomes 38718).
Is there a way to stop this happening or a function within acces that changes the value back to a date format?
Cheers,
Spinkung
I need to import 1600 people from excel.
as the program that we use to use was free and setup for what we needed. That has the ability to create a excel sheets with the 1600 people on.
Now all the fields are different, and i'm not sure how to do it.
When importing do you get the option to put the fields in the correct place, or do i need to edit them? and then import.
Another thing the address layout with fields will be completely wrong. I believe it used separate fields for each line of the address where in my DB i use 2 fields 1 address and 1 postcode.
Hi all,
I have been using Excel data in Access either by importing the sheet as a table or directly linking to the sheet with no problems in the past but today I cannot seem to get the data to import in the right way.
The column on the Excel sheet is mainly populated by numbers but some of the values have letters in them. When I discovered this I formatted the column as text and tried to directly link to the sheet but the datatype kept insisting it is number. I then decided to import the sheet as a table and although it comes in as Text instead of seeing the value A1030573 in comes in as 7.1e+009 and this goes for the values that are purely numbers.
Is this a known bug, does anyone have a solution to to this ?
Thanks in advance,
Mitch....
Hello all,
Hope this is the right section to post this question. I thank all for any assistance in advance.
We basically have a supplier's access database that contains one table of prices.
In addition, there also exists an excel file that contains applicable discounts against the supplier's items. There's a common key, the part number.
There's a need to unify these on an on-going basis as supplier sends updates to their database, and our finance department (who will not move away from an excel worksheet) update their records. What I'm thinking of doing is creating another database with two tables; one whose characteristics are same as the supplier's table and another with fields matching the excel file. And write a script or something that when you execute it, pulls data from (predetermined location) database and excel file to the two tables. Queries can then be constructed using the relationship between the tables.
How can this be achieved? Did a search and no sugar. Or is there a better way. The users are access novices and would like to minimise their "workload".
Cheers
Hi... i have a excel file..... from which i have imported records.... after importing.... the records sequence in my table has changed..... can anyone tell me how to preserve the sequence that i had in excel..........
View 3 Replies View Related