Problems importing data from Excel to Access

[jewart17]

I am a novice with Access, so please bear with me. Here are the basics of what I am trying to do, and the problem I am encountering:

1. I have a .txt file named Dailystats.txt

2. I have an Excel workbook named Dailystats.xls with an Auto_open macro that imports the data from Dailystats.txt, formats the data, and then saves and closes the Excel workbook.

3. I have an Access macro which open the Excel file, and then imports the resulting data into my Access table.

4. The process works fine the first time I run it, but once I edit the .txt file and run through the entire process a second time, Access imports the original set of data. If I then run the same macro in Access a second time, it imports the correct data.

5. I've gone through each step individually to ensure that the .txt file and the Excel macro are functioning properly, and they are. The problem only exists on the first attempt of running the Access macro with new data. On the second try, the data imports correctly.

6. I've created a delete query to strip out the duplicated records that are created the first time I run the Access macro, but I'd like to figure out the correct solution.

Can anyone help?

 

[PHV]

Can anyone help?
Without seeing any code, I'm afraid the reply is: No

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jewart17]

Sorry about that, should have included that the first time. Here is the Access code:

Function mcr_importphonestats()
On Error GoTo mcr_importphonestats_Err

Call Shell("excel.exe ""C:\Documents and Settings\jewartj\My Documents\Stats\Daily CSR Production\Daily Stats.xls""", 1)
DoCmd.TransferSpreadsheet acImport, 8, "tbl_phonestats", "C:\Documents and Settings\jewartj\My Documents\Stats\Daily CSR Production\Daily Stats.xls", True, "A1:R8"
DoCmd.OpenQuery "qry_delblank", acViewNormal, acEdit
DoCmd.OpenQuery "qry_deldups", acViewNormal, acEdit


mcr_importphonestats_Exit:
Exit Function

mcr_importphonestats_Err:
MsgBox Error$
Resume mcr_importphonestats_Exit

End Function

Here is the Excel code:

Sub Autpen()
'
' Autpen Macro
' Macro recorded 6/10/2009 by jewartj
'

'
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\jewartj\My Documents\Stats\Daily CSR Production\Stats.txt" _
, Destination:=Range("A1"))
.Name = "Stats"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Application.CommandBars("External Data").Visible = True
Selection.QueryTable.Refresh BackgroundQuery:=False
Application.CommandBars("External Data").Visible = False
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Rows("3:3").Select
Selection.Delete Shift:=xlUp
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("B1").Select
Selection.Cut
Application.CutCopyMode = False
Selection.ClearContents
Range("A2").Select
ActiveCell.FormulaR1C1 = "Date"
Range("A2").Select
ActiveCell.FormulaR1C1 = "Activitydate"
Range("A3").Select
Columns("A:A").EntireColumn.AutoFit
ActiveCell.FormulaR1C1 = "=R[-2]C[2]"
Range("A3").Select
Selection.Copy
Range("A3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=IF(RC[2]>0,R[-2]C[2],"""")"
Range("A3").Select
Selection.AutoFill Destination:=Range("A3:A9"), Type:=xlFillDefault
Range("A3:A9").Select
Range("A3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[2]>0,R1C3,"""")"
Range("A3").Select
Selection.Copy
Range("A4:A9").Select
ActiveSheet.Paste
Columns("A:A").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A3").Select
Application.CutCopyMode = False
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Range("C2:C13").Select
Range("C13").Activate
Selection.NumberFormat = "0"
Columns("S:CU").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.LargeScroll ToRight:=-1
Windows("Daily Stats.xls").Activate
ActiveWorkbook.Save
Application.Quit
End Sub

 

[MasterRacker]

Is there a reason you're importing text into Excel then Excel into Access instead of simply importing the text data directly into Access? It would certainly simplify the process.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[jewart17]

The program that is generating the .txt file exports it in a way that will not allow it to properly import into Access. I need to modify the data in Excel prior to importing it into Access.

 

[kjv1611]

How is it that you can't import into Access, but can into Excel? Can you give us a little more information? There may be a 100% Access method to fix it.

--

"If to err is human, then I must be some kind of human!" -Me

 

[Jewel142]

I do this all the time with txt files.

Open up Excel, select your txt file and open the file. The Text Import Wizard should pop up. You can then import your txt file into Excel and define your fields. Depending on how your file is formatted, you can specify if it is delimited or fixed width data type. Then you can save it in Excel and import it into access.

When you import it into access use the import wizard. Select your spreadsheet (you may need to use the drop down list and tell it to look for .xls files. Click import. The import spreadsheet wizard will open showing your named worksheets. Select the one that you want and hit next. You want to select "In a New Table". Click Next and it will ask you which files you want imported. If you want all of them, click next. The next screen asks you if you want to have access define your primary key or if you want to define it - choose the option that best meets your needs. Click next and it will ask you what table you want to import it to. Select the table name and then hit finish. It will ask you if you want to overwrite the existing table or Query? Select yes. This will overwrite all the data in your table thereby refreshing it.

 

[MasterRacker]

I agree with kjv1611. I'd still like to know what you're doing in Excel that you can't do directly in Access.

In my experience Access is a much better tool for low level data manipulation than Excel. Especially if you're concerned with specific data types, string manipulations etc.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[Jewel142]

I agree. It just seemed like he wanted the data brought in to Excel first. This part of the original post almost leads me to believe that the original table isn't being completely overwritten the first time and is resulting in the duplicates

4. The process works fine the first time I run it, but once I edit the .txt file and run through the entire process a second time, Access imports the original set of data. If I then run the same macro in Access a second time, it imports the correct data.

I've created a delete query to strip out the duplicated records that are created the first time I run the Access macro, but I'd like to figure out the correct solution.

 

[cwhite23]

One thing that I've run into in importing text files is that if they are comm-seperated value files (not true text), access will be thrown off by any extra commas. For example, if you have a text file with the following data:

John Smith, 10/13/09, Customer

Access will import it as three columns; one for the name, one for the date, and one for the notes field. If the data is formatted like this:

Smith, John, 10/13/09, Customer

you're going to end up with four coulumns - 'Smith' will go into the Name filed, 'John' will go into the Date field, the date will go into the notes field, and 'Field1' will be added to the remainder.

I actually had to pull the data into Excel, save it as a spreadsheet, and then import that spreadsheet to get everything to land where it needed to.

Don't know if that's what you're running into, but maybe this will help explain thing a bit.