Solved

import csv file into Access via VBA

Posted on 2011-03-15
4
790 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
ID: 35137845
   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
ID: 35138041
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
ID: 35138157
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
ID: 35138405
Thank you
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

808 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