Import Wizard (Excel Correct Format)
Jan 26, 2007
I am trying to import an Excel2002 file into Access2002 using the import wizard. There is a hidden worksheet named 'Data' with 5 ranges defined (Page1-5), one of which I am trying to import: I am getting the following error message –
“Import Spreadsheet Wizard -- The wizard is unable to access information in the file ‘D:xxxx.xls’. Please check that the file exists and is in the correct format.”
I have successfully imported other Excel files from the same folder, but using a different original file. The original Excel file giving me the trouble was created in 1999 but has been saved with Excel2002 version.
Can anyone tell me what causes the “correct format” error? I am confident it is something having to do with the Excel file. I've attached a zip file with the problem workbook.
Thanks in advance for any assistance.
View Replies
ADVERTISEMENT
Apr 14, 2008
Hey Guys,
I read several threads for suggestions, and most of them ask to import raw data into a temp table then append. But thought I see if this would be possible instead. The following codes imports all excel files in a folder, and extracts the date from the file name and puts that into a field in the table. And also moves the imported file to an archive folder after import.
Private Sub btnImport_Click()
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
Dim path As String
Dim TheDate As Date
Dim fs
DoCmd.SetWarnings False
path = "C:UsersChinaboyDesktopData" ' Path that stores Historical Report Downloaded files.
'Loop through the folder & build file list
strFile = Dir(path & "*.xls")
While strFile <> ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend
'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
Exit Sub
End If
'cycle through the list of files
For intFile = 1 To UBound(strFileList)
strFile = path & strFileList(intFile)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tblAgentSummary", strFile, False
'Adds date to callDate field based on the date on file name.
TheDate = Mid(strFile, 54, 2) & "/" & _
Mid(strFile, 56, 2) & "/" & _
Mid(strFile, 58, 4)
CurrentDb.Execute "UPDATE tblAgentSummary SET callDate =" & "'" & TheDate & "' where callDate is null"
'set directory to look for next text file
Next intFile
'Moves imported file to Archive folder
Set fs = CreateObject("Scripting.FileSystemObject")
fs.MoveFile "C:UsersChinaboyDesktopData*.xls", "C:UsersChinaboyDesktopArchives"
DoCmd.SetWarnings True
End Sub
Is it possible for me to open each excel file in thebackground in the folder, format it before or during the import process. What I need to do is first delete rows 1 and 2, delete column B, D, F, and I. Then move to the last row with data then delete that row along with the previous 2.
I found this code that I thought may help, but I am not sure how to work it in with my exsiting code. Any suggestion and idea will be greatfully appreciated.
Private Sub Command0_Click()
Dim xlApp As New Excel.Application
Dim xlwrkBk As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set xlwrkBk = xlApp.Workbooks.Open("C:Documents and Settings34036460 Project Files140 Excel AutomationXOR27NovB.xls")
Set xlSheet = xlwrkBk.Worksheets("OpenExchangeOrders27Nov")
xlSheet.Rows(1).Delete
xlwrkBk.Save
xlwrkBk.Close
Set xlSheet = Nothing
Set xlwrkBk = Nothing
xlApp.Quit
Set xlApp = Nothing
MsgBox "Done"
End Sub
View 6 Replies
View Related
Aug 16, 2007
im trying to import the attached excel file to access table. I got error message says "type conversion error". the date column after 07/13/2007 can not be imported, but anything before that date can. I formated everything in excel with the same format, how did this happen?
Thanks.
View 1 Replies
View Related
Jan 31, 2008
i have 1 table that link to excel file. the excel file is generate by other software and it's on excel 2.1 format. I've tried to directly link to the file but not working. so i manually open the file and save the file on excel 2002 format, then i use that file to be linked on my mdb. Unfortunately every time i replace the file with the newest data, my mdb not displaying the updated data.
- how can i link old version of excel on mdb?
- what should i do so my mdb will display the updated data after the excel file is replaced?
thanks
View 3 Replies
View Related
Nov 14, 2014
I have an excel table called Parameter with a column called "Test" -- The column contains integer numbers only. So all the numbers in the column are like 5,10,15,20 etc..I have an access macro which imports the entire excel table into a access table called dbo_Parameters
I have created an access macro to run "Saved Import" for 'dbo_Parameters"..After uploading, all the data in the column Test is formatted to mm/ dd/ yyyy. The Field Size is Integer, but the format is view format is converted to a Date..I have to change the properties of the column to "General Number" and get rid of the date format.how to change the import format!
a) The format of the column in Excel - It is number
b) The import procedure and saved import. I am unable to change the format of the import during upload. I make sure that the import format column is Integer
View 1 Replies
View Related
Jun 25, 2014
I am trying to format a spreadsheet to import into a Table. To do this I need to delete the top 8 rows and then the 4 rows below the data I need, both areas contain header data. The 4 rows below the data I need are blank but formatted oddly and it is causing issues during import.
Code:
Sub ExcelFormat()
Dim excelApp As Object
Set excelApp = CreateObject("Excel.Application")
excelApp.worbooks.Open ("Z:DataTest.xlsx")
End Sub
View 11 Replies
View Related
Jan 6, 2005
Wondering if anyone can advise...
The 1st/2nd Jan of this year were treated by most people as still being week 53 of 2004, and week commencing 3rd January being week 1 of 2005. In VB I have a formula to do this:
WeekNo = Format(Now(), "ww", vbMonday, vbFirstFourDays)
However my problem now is that I need to replicate this format in a Query within Access. The formula
WeekNo: Format(Date(),"ww")
treats 1st/2nd Jan as week 1, the 3rd as week 2 etc. Any idea how I can get it to treat 3rd Jan as week 1 instead?
There's a challenge for someone!! Help appreciated.
View 1 Replies
View Related
Nov 8, 2013
I'm trying to pass some dates from an excel userform into access.
The date is chosen using the DTPicker tool ( basically a drop down calender). I have set the property of this to custom format dd/MM/yyyy, however dates get passed to the appropriate field in access in the American format.
In access the date fields are set to Short Date and the example shown for this format is in the UK format. I assign the date to a variable before passing that variable to the update SQL string:
Code:
s1 = Nz(DTPicker1.Value, #1/1/2000#)
I have dimmed s1 as date and then added:
Code:
s1 = Format(Date, "dd/MM/yyyy")
My update string is:
Code:
"SET [Stage 1] = " & "#" & s1 & "#" & " "
I suspect that the nozero function may be the issue but am at a bit of loss atm.
View 11 Replies
View Related
May 7, 2013
So I have a table with around 20,000 records and there is a field which holds a phone number for each individual within the organization, I want to run a select query that counts how many are not in the correct format
The format is 10 digits and it must start with a 0
Using Access 2010
View 2 Replies
View Related
Dec 17, 2013
How to get this expression to work? It works by changing the numbers to correct date format, however, if the field is null, I want a blank to appear instead of "type"
=IIf([DATE_APPLIED_X] Is Null,"""",CDate(Mid([DATE_APPLIED_X],5,2) & "/" & Right([DATE_APPLIED_X],2) & "/" & Left([DATE_APPLIED_X],4)))
Report.JPG
View 10 Replies
View Related
Aug 28, 2014
Is it possible to have a command button on a form to run the Text File Import Into a Table Wizard?
View 13 Replies
View Related
Mar 2, 2012
The code I have is.
Code:
Private Sub Command26_Click()
If Forms![test site]![prp test].Form.[A Right Answer] = -1 Then
Forms![test site]![number correct] = Forms![test site]![number correct] + 1
End If
DoCmd.FindNext
End Sub
Then when clicked it checks a yes/no box to see if "A right Answer" is the correct yes. Then it should pop to the main form and take the number correct cell and add one to it. I am trying to get the record to go to the next record inside the sub-form but docmd.findnext seems to be wrong too.
View 4 Replies
View Related
Jun 19, 2007
Hi:
Working with a bank that wants a file from us so they can import into their check reconciliation program. Got the export from the accounting program working for the info they need, but the bank guy says he needs a file structure like this:
(first row is header..don't worry about what the stuff means for now)
1234578990000000000000000
(rest of rows are details)
1234589000807091234
1234589000807093456
notice the line breaks between header and rows? Wouldn't this call for a line break and mess up an import program? Bank guy isn't a database person and is clueless
Thanks......
View 2 Replies
View Related
Mar 8, 2012
I have a database where I need to import data from an excel file.
What my employer needs me to do is upon appending the data to a table which I named properties, under the field which I called owner, all the names should follow the correct format. Most of the inputted data does not follow the correct format.
The correct format of the names should be:
K. CURTIZ -> Where K is the initial of the First Name, it should have the '.' and a space before the Family Name
Here's an example of a data that is inputted incorrectly:
R OTTESON -> it should be R. OTTESON
another example where there are 2 names of 2 different persons, it should be inputted this way:
A. & J. PANAG
Mostly inputted incorrectly this way:
J&T BOTIFAN -> it should be J. & T. BOTIFAN
View 3 Replies
View Related
Aug 28, 2004
I am stumped: After importing from an outside source (an excel spreadsheet) I have to change the short-date format
that I have just imported into a very specific custom format: "yyyymmdd" Ex: 20040828. How do I convert into this
new format? Help will be greatly appreciated...
View 1 Replies
View Related
Apr 12, 2014
I've written a vba macro that takes data from Excel, processes it into an XML file & then imports into Access.All fields were fine apart from the date field, which appeared in the Access table as "00:00:00"...The date in the XML was appearing as 01/01/2014.
Code:
dateTemp = [date here]
xmlFieldElement.Text = Year(dateTemp) & "-" & Format(Month(dateTemp), "00") & "-" & Format(Day(dateTemp), "00") & "T00:00:00"
This produces 2014-01-01T00:00:00...Note, putting the date into reverse order without the formatting also causes the import to result in 00:00:00
View 1 Replies
View Related
Jun 17, 2014
I am building a database using data imported from Excel workbooks. The dates in the workbooks are formatted as text in the YYYYMMDD format. Is there a way to convert this into date format during the import or after? I am pulling in a lot of different workbooks and trying to avoid having to reformat each individual workbook prior to importing them.
View 3 Replies
View Related
Sep 9, 2013
How to Export and Import data into and from Access using a Quicken Interchangeable Format (QIF) ...
View 4 Replies
View Related
Mar 22, 2006
Hi all,
I'm trying to create a chart in a form, this is not a problem, however, the chart types available are a bit limited compared to if i inserted a Microsoft Excel Chart object.
I'd do that except I'm trying to create a chart based on a query.
Is there a way to make the chart wizard use the Microsoft Excel Chart object as its chart creator so I have access to the chart types available in that object?
any assistance much appreciated.
thanks all.
regards
keji
View 1 Replies
View Related
Oct 8, 2013
I upgraded to Access-2010 and the Text File Import function will not recognize dates in the format YYYY-MM-DD. The import dialogue sees enough to recognize the field as a date, but then every date encountered is written to the Import-Errors table. This is true whether the file has a .txt or .csv extension. The actual file format is .csv.
View 6 Replies
View Related
Sep 19, 2006
Hi,
Can someone advise how to import from excel. include the heading.
thanks
View 1 Replies
View Related
Jan 12, 2008
I think this is an age old question but I have yet to ask it. I have a XLS spreadsheet I'm importing into a table in access. One of the column in excel has both the first and last name and middle intial sometimes such as Frank H. Urtz. I want to seperate this in access. firstname =Frank lastname=Urtz midinitial=H. How do I go about it?
I think I want to write a query saying give me everything up to first " ". Insert that into field1. GThe give me everything from the first ' " to the second " " and insert into field2 and so on.
Any ideas?
View 4 Replies
View Related
Dec 16, 2004
This is a bit of a tricky one to explain, so please bear with me:
I have a table set up that I regularly import data into using the docmd.transfersheet command.
The problem I am having is that one of the columns I want to import as type text length 3 (the field in the table is set as this)… This column (in excel) has values in such as 001, 121, 999, T10. If the values in the first 10 or so rows are full numbers i.e 121 or 999 then the import command takes this column as being a double. Meaning that it misses out when it gets to values of 001 or T10 etc… and replaces them with NULL
If I reorder the excel sheet before importing and make sure a text value (i.e 091 or T10) is near the top the import works fine and all the data is imported.
I have also tried manually importing the data into the table and a new table and both give the same results.
The question, how can I force access to import that column of the sheet as a text field?
I have tried everything I can think of and have failed miserably.
Any help would be greatly appreciated.
View 7 Replies
View Related
Sep 3, 2013
I saw a demo that the user could copy and paste a JPG format file physically to ACCESS, and then something happened, and ACCESS could read the JPG length and width size (e.g. 1.3 meter length and 1.15 meter width) into 2 individual ACCESS cells which is acted the same as user input to that 2 clells.
View 14 Replies
View Related
May 5, 2005
hi,
I'm having a small but annoying problem exporting data to excel and then importing it back..
The problem is that when i export a table to Excel - it changes the date format from dd/mm/yy (which is what i have in Access) to DD-MMM-YY (automatically in Excel)
This proves to be a problem because when i import the spreadsheet back Access gives me a data type conversion error.
Its easily solved manually by changing the field in excel to dd/mm/yy format but annoying as the user will have to do this everytime they export and import!
Any suggestions??
Thanks in Advance..
View 6 Replies
View Related
Sep 27, 2005
I have used the search and it proved to be very helpful, as always. However, I still have an issue with my coding, it keeps trying to open my Excel file I have imported after I have closed it (or at least I think I have). Here is the code (copied right from another thread with a few tweaks):
Option Explicit
Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type
Private Sub Command0_Click()
Dim OpenFile As OPENFILENAME
Dim lReturn As Long
Dim sFilter As String
Dim WrksheetName As String
Dim oApp As Object
OpenFile.lStructSize = Len(OpenFile)
OpenFile.hwndOwner = Form.Hwnd
sFilter = "acSpreadsheetTypeExcel9 (*.xls)" & Chr(0) & "*.xls" & Chr(0)
OpenFile.lpstrFilter = sFilter
OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = String(257, 0)
OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = "C:"
OpenFile.lpstrTitle = "Select the Information to Import"
OpenFile.flags = 0
lReturn = GetOpenFileName(OpenFile)
If lReturn = 0 Then
Exit Sub
End If
Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
oApp.Workbooks.Open OpenFile.lpstrFile
With oApp
.Visible = True
WrksheetName = "Import"
DoCmd.TransferSpreadsheet (acImport), acSpreadsheetTypeExcel9, WrksheetName, OpenFile.lpstrFile, True
End With
oApp.Workbooks.Close
oApp.Quit
Set oApp = Nothing
End Sub
View 5 Replies
View Related
