Solved

import csv file into Access via VBA

Posted on 2011-03-15
4
786 Views
Last Modified: 2012-05-11
I am using the following code to import an Excel File. I need import a csv comma delimited. How how do I need to change this

    With fd
        .Title = "Select file for import"
        .Filters.Add "XLS File", "*.xls"
        .InitialFileName = INITIAL_FOLDER
        If .Show = -1 Then FilePath = .SelectedItems(1)
    End With
    Set fd = Nothing
     
        docmd.TransferSpreadsheet , acSpreadsheetTypeExcel3, "Table1", FilePath, True
0
Comment
Question by:Pdeters
  • 2
  • 2
4 Comments
 
LVL 4

Expert Comment

by:ruslanin
Comment Utility
   With fd
        .Title = "Select file for import"
        .Filters.Add "XLS File", "*.xls"
        .InitialFileName = INITIAL_FOLDER
        If .Show = -1 Then FilePath = .SelectedItems(1)
    End With
    Set fd = Nothing

    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & FilePath, Destination:=Range("$A$1"))
        .Name = "CSV Data"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 65001
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
0
 

Author Comment

by:Pdeters
Comment Utility
I am looing to pulli n a csv file

   docmd.TransferSpreadsheet , acSpreadsheetTypeExcel3, "Table1", FilePath, True

The file will be in the same format each time
0
 
LVL 4

Accepted Solution

by:
ruslanin earned 500 total points
Comment Utility
Sorry, not clear understand before

try to this command

docmd.TransferText acImportDelim,"Table1",FilePath,True

more information you can find here:
http://msdn.microsoft.com/en-us/library/bb214141(v=office.12).aspx

0
 

Author Closing Comment

by:Pdeters
Comment Utility
Thank you
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now