Open CSV file from MS Access

F

forrestgump

Registered User.
Local time
Today, 06:01
Joined
Aug 14, 2017
Messages
10
Hi all,

I am trying to open a blank excel workbook from MS Access and then bring in the data from another excel file. I need to do this because of the file i need to open is a csv file and if I open it normally i lose leading zeros. I have tried the below code but i cant get it to work. Any help would be greatly appreciated.

Code: 
 Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Set xlApp = New Excel.Application
    Set xlWB = xlApp.Workbooks.Add
    xlApp.Visible = True
    xlApp.Sheets("Sheet1").Select
    xlApp.Sheets("Sheet1").name = strFileName

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\jsmith\Documents\002_Department.csv", Destination:=Range( _
"$A$1"))
.Name = "002_Department"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
 
Have you tried "Reading" the *.CSV file?

Sent from my SM-G925F using Tapatalk
 
Both posts moderated, now approved. Posting to trigger email notifications.
 
You're missing the reference to Excel in the ActiveSheet code line.
Code: 
With [B][COLOR=Red]xlApp.[/COLOR][/B]ActiveSheet.QueryTables.Add(Connec
There could be more errors but then post back with the error message + in which code line it appear.
Where do you declare the variable strFileName and where do you set the value of it?
Code: 
 xlApp.Sheets("Sheet1").name = [B][COLOR=Red]strFileName[/COLOR][/B]
 
You mention doing this in 2 stages to prevent loss of leading zeroes in your CSV file. You can solve this by explicitly importing as text fields so they aren't being 'helpfully' converted to numbers.

Code: 
CStr(fieldname)

Then you can do it all in one step
 
Years ago I had a spreadsheet import problem where the wrong format was imported. The solution which worked for me was to add a row at the beginning of the spreadsheet.
In this row include values which represent the format you want the rest of the values in the columns to be imported as. In your case add text for example "xxxx"...

Sent from my SM-G925F using Tapatalk
 
ridders said:
You mention doing this in 2 stages to prevent loss of leading zeroes in your CSV file. You can solve this by explicitly importing as text fields so they aren't being 'helpfully' converted to numbers.

Code: 
CStr(fieldname)

Then you can do it all in one step
Click to expand...

Can you expand on the above how do I import a file into access using this?

Kind regards,
Forrestgump
 
JHB said:
You're missing the reference to Excel in the ActiveSheet code line.
Code: 
With [B][COLOR=Red]xlApp.[/COLOR][/B]ActiveSheet.QueryTables.Add(Connec
There could be more errors but then post back with the error message + in which code line it appear.
Where do you declare the variable strFileName and where do you set the value of it?
Code: 
 xlApp.Sheets("Sheet1").name = [B][COLOR=Red]strFileName[/COLOR][/B]
Click to expand...

Hi there,

I tried the above, unfortunately I couldn't get it to work. Any ideas?

Regards,

Forrestgump
 
I'm confused.

Your thread was asking about importing data from Access into Excel:

I am trying to open a blank excel workbook from MS Access and then bring in the data from another excel file. I need to do this because of the file i need to open is a csv file and if I open it normally i lose leading zeros.
Click to expand...

Now you're asking about importing into Access
forrestgump said:
Can you expand on the above how do I import a file into access using this?
Click to expand...

And you've also just created a new almost identical thread

As I'm unclear what to answer, I'll do the import into Access answer.

Here's a fairly complicated example in which:
a) the CSV file is first imported into a buffer table - ImportedCourses
b) the data is then modified to ensure correct datatypes & imported into the final table - Courses
c) the changes are logged to another table - LogOfImportedData
You can probably ignore this bit

Code: 
Set mydb = CurrentDb
Set myset = mydb.OpenRecordset("Courses", dbOpenDynaset, dbSeeChanges)
Set myset1 = mydb.OpenRecordset("ImportedCourses", dbOpenSnapshot)
Set MySet2 = mydb.OpenRecordset("LogOfImportedData", dbOpenDynaset, dbSeeChanges)

With myset1
If .RecordCount > 0 Then
    .MoveFirst
    Do Until .EOF

        If Not Nz(!CourseID, "") = "" Then
        strCourse = !CourseID
        myset.FindFirst "CourseID = '" & !CourseID & "'"
        If myset.NoMatch Then
            myset.AddNew
            myset!CourseID = !CourseID
            myset!Year = CInt(Nz(!Year, ""))
            myset!SubjectID = CStr(Nz(!SubjectID, ""))
            myset!ShowOnReport = -1
            myset.Update
            
            MySet2.AddNew
            MySet2!EntryDate = Now
            MySet2!ObjectName = "Course"
            MySet2!ItemReportID = 2
            MySet2!ItemText = "Course " & !CourseID & " added"
            MySet2!ItemAction = "Added"
            MySet2.Update
        Else
            myset.Edit
            If Nz(myset!SubjectID, "") <> CStr(Nz(!SubjectID, "")) Then
                strSubject = Nz(myset!SubjectID, "null")
                myset!SubjectID = !SubjectID
                MySet2.AddNew
                MySet2!EntryDate = Now
                MySet2!ObjectName = "Course"
                MySet2!ItemReportID = 2
                MySet2!ItemText = "Course " & !CourseID & ": Subject changed from " & strSubject & " to " & !SubjectID
                MySet2!ItemAction = "Modified"
                MySet2.Update
            End If
            
            If Nz(myset!Year, 0) <> CInt(Nz(!Year, "")) Then
                intYear = Nz(myset!Year, 0)
                myset!Year = CInt(Nz(!Year, ""))
                MySet2.AddNew
                MySet2!EntryDate = Now
                MySet2!ObjectName = "Course"
                MySet2!ItemReportID = 2
                MySet2!ItemText = "Course " & !CourseID & ": Year changed from " & intYear & " to " & !Year
                MySet2!ItemAction = "Modified"
                MySet2.Update
            End If
            
            myset.Update
        End If
        End If
        .MoveNext
    Loop
End If
End With

    WriteTextEntry strText4
    If IsLoaded("frmMain") Then Forms!frmMain.LblHelpText.visible = False

You should be able to find the relevant bits in the above
 
Last edited:
Hi thanks for your reply

What I am trying to achieve is:-

open excel from access
then open the csv file in excel so i can edit

Does that make sense?
 
I think you need to heed the advice given - which is to basically import the CSV directly to a temporary "staging" table, setting every field to text.
You can then manipulate that data exactly as you need to to get it into your final destination, in the format you need.

Ridders post shows how to do this.
 
forrestgump said:
Hi thanks for your reply

What I am trying to achieve is:-

open excel from access
then open the csv file in excel so i can edit

Does that make sense?
Click to expand...

No - its illogical to do that

Assuming you want to end up with the data in Access (otherwise why else would you start from there), you should do it the way I've explained.
You can then edit the data in Access - either in the 'buffer table' before final import or after importing to the final table
 
Unfortunately, I wont know how to do all the manipulation in Access that i need to do. Which causes other issues.
 
I found the reason for what was going wrong i needed to put xlapp in the destination part

Code: 
Destination:=xlApp.Sheets("Sheet1").Range("A1")
 
isladogs said:
I'm confused.

Your thread was asking about importing data from Access into Excel:



Now you're asking about importing into Access


And you've also just created a new almost identical thread

As I'm unclear what to answer, I'll do the import into Access answer.

Here's a fairly complicated example in which:
a) the CSV file is first imported into a buffer table - ImportedCourses
b) the data is then modified to ensure correct datatypes & imported into the final table - Courses
c) the changes are logged to another table - LogOfImportedData
You can probably ignore this bit

Code: 
Set mydb = CurrentDb
Set myset = mydb.OpenRecordset("Courses", dbOpenDynaset, dbSeeChanges)
Set myset1 = mydb.OpenRecordset("ImportedCourses", dbOpenSnapshot)
Set MySet2 = mydb.OpenRecordset("LogOfImportedData", dbOpenDynaset, dbSeeChanges)

With myset1
If .RecordCount > 0 Then
    .MoveFirst
    Do Until .EOF

        If Not Nz(!CourseID, "") = "" Then
        strCourse = !CourseID
        myset.FindFirst "CourseID = '" & !CourseID & "'"
        If myset.NoMatch Then
            myset.AddNew
            myset!CourseID = !CourseID
            myset!Year = CInt(Nz(!Year, ""))
            myset!SubjectID = CStr(Nz(!SubjectID, ""))
            myset!ShowOnReport = -1
            myset.Update
           
            MySet2.AddNew
            MySet2!EntryDate = Now
            MySet2!ObjectName = "Course"
            MySet2!ItemReportID = 2
            MySet2!ItemText = "Course " & !CourseID & " added"
            MySet2!ItemAction = "Added"
            MySet2.Update
        Else
            myset.Edit
            If Nz(myset!SubjectID, "") <> CStr(Nz(!SubjectID, "")) Then
                strSubject = Nz(myset!SubjectID, "null")
                myset!SubjectID = !SubjectID
                MySet2.AddNew
                MySet2!EntryDate = Now
                MySet2!ObjectName = "Course"
                MySet2!ItemReportID = 2
                MySet2!ItemText = "Course " & !CourseID & ": Subject changed from " & strSubject & " to " & !SubjectID
                MySet2!ItemAction = "Modified"
                MySet2.Update
            End If
           
            If Nz(myset!Year, 0) <> CInt(Nz(!Year, "")) Then
                intYear = Nz(myset!Year, 0)
                myset!Year = CInt(Nz(!Year, ""))
                MySet2.AddNew
                MySet2!EntryDate = Now
                MySet2!ObjectName = "Course"
                MySet2!ItemReportID = 2
                MySet2!ItemText = "Course " & !CourseID & ": Year changed from " & intYear & " to " & !Year
                MySet2!ItemAction = "Modified"
                MySet2.Update
            End If
           
            myset.Update
        End If
        End If
        .MoveNext
    Loop
End If
End With

    WriteTextEntry strText4
    If IsLoaded("frmMain") Then Forms!frmMain.LblHelpText.visible = False

You should be able to find the relevant bits in the above
Click to expand...
Thanks
 
You must log in or register to reply here.

Similar threads

D
Solved Formatting Excel File from Access
Replies
5
Views
910
Eugene-LS
Eugene-LS
D
Solved Creating Excel Files from Access
Replies
9
Views
868
arnelgp
arnelgp
W
CSV or XLSX Bank Statement to Access module (need to import via VBA and then write back to Excel with imported flag)
Replies
14
Views
1,523
Cotswold
Cotswold
L
Solved Extract excel from access
Replies
4
Views
838
Leo_Polla_Psemata
L
L
Export data from Access to Word with VBA
Replies
12
Views
1,216
arnelgp
arnelgp

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom