Function Application.GetOpenFilename

Solved

mamined Posted messages 37 Status Member -
mamined Posted messages 37 Status Member - 23 Jun 2020 at 05:19

Hello group,

I built the code below while watching videos on YouTube (yes, I'm not skilled..)
So the code works and everything is fine with one exception.
I open a CSV file and it opens in Excel, which is a problem because the data is combined together and separated by a "|" separator but also has ";" and ":" so it would be necessary for my code not to open it with Excel but with a text editor like WordPad or Notepad.

How to modify my code so that when it indicates: Set MonClasseur = Application.Workbooks.Open(ListFile) it does so with one of the 2 types of files mentioned above?

Thank you in advance for your help

Option Explicit Sub AutoImportDataTXT() 'variable declarations Dim ListFile As Variant Dim MonClasseur As Variant 'disable alerts Application.ScreenUpdating = False Application.CutCopyMode = False 'filter to see only the type of file to select ListFile = Application.GetOpenFilename(Title:="Select data and import data", fileFilter:="CSV Files(*.csv*),*xls*", ButtonText:="Click") 'handle the case of the cancel button click in case of error If ListFile <> False Then 'indicate the selected file Set MonClasseur = Application.Workbooks.Open(ListFile) 'copy data MonClasseur.Sheets(1).Range("A1").CurrentRegion.Copy 'paste data ThisWorkbook.Sheets("sample").Range("A1").PasteSpecial xlPasteValues 'close source workbook MonClasseur.Close End If End Sub

3 answers

Answer 1 / 3
yg_be Posted messages 23437 Registration date Status Contributor Last intervention Ambassador 1 588

Hello, if you don't open it in Excel, you won't be able to use its data from your program.
0
1. mamined Posted messages 37 Status Member
  
  Hello yg_be
  
  thank you for your reply but I do not understand why I cannot use the data since I do it manually and it works very well.
  So in my opinion, we should be able to automate it.
  
  Process:
  
  Select csv file
  right click
  open with
  Select notepad or other text editor
  press ctrl A + ctrl C
  go to my Excel sheet (where I have my code in the right sheet in A1)
  ctrl V
  
  so if it works like that, we should be able to automate it in my opinion.
  
  0
  1. yg_be Posted messages 23437 Registration date Status Contributor Last intervention 1 588 > mamined Posted messages 37 Status Member
    
    Have you ever tried to do this process in VBA?
    I suggest that you only use Excel instead, maybe by working a little more on it to transform the data.
    
    0
  2. mamined Posted messages 37 Status Member > yg_be Posted messages 23437 Registration date Status Contributor Last intervention
    
    Hello,
    
    actually, as I said, I can't with Excel. It's not working, the reason is simple. If I open my csv in Excel, I have split columns while I shouldn't have them split. Look at the 2 images and you'll understand the problem.
    
    0
  3. yg_be Posted messages 23437 Registration date Status Contributor Last intervention 1 588 > mamined Posted messages 37 Status Member
    
    You couldn't do it with Excel, it doesn't mean it's not possible.
    This might help you: https://docs.microsoft.com/fr-ch/office/vba/api/excel.workbooks.open
    Otherwise, you can quite easily manipulate and transform data in VBA.
    
    0

cs_Le Pivert Posted messages 8437 Status Contributor 730

Hello,

to save a csv file into a txt file:

Option Explicit Sub traitement() Dim Chemin_Fichier_CSV As String Dim Chemin_Fichier_TXT As String Dim Buffer Chemin_Fichier_CSV = ThisWorkbook.Path & "\Customers.csv" 'adapt csv name Chemin_Fichier_TXT = ThisWorkbook.Path & "\essai.txt" 'adapt txt name Close Open Chemin_Fichier_CSV For Input As #1 Open Chemin_Fichier_TXT For Output As #2 Do While Not EOF(1) Line Input #1, Buffer Print #2, Buffer Loop Close End Sub

--
@+ The Pivert

yg_be Posted messages 23437 Registration date Status Contributor Last intervention 1 588

Why not copy the file under a different name?
0

cs_Le Pivert Posted messages 8437 Status Contributor 730

The data is grouped together and separated by a separator ""|"" but also has "";"

Therefore, it is necessary to replace the ; with |

Here is a code that saves the CSV as text with the tabulator |. Then it imports the text file into an Excel workbook:

Option Explicit 'Save CSV in text file with tabulator | Sub treatment() Dim CSV_File_Path As String Dim TXT_File_Path As String Dim Buffer, str As String CSV_File_Path = ThisWorkbook.Path & "\Customers.csv" 'adapt CSV name TXT_File_Path = ThisWorkbook.Path & "\test.txt" 'adapt TXT name Close Open CSV_File_Path For Input As #1 Open TXT_File_Path For Output As #2 Do While Not EOF(1) Line Input #1, Buffer Print #2, Replace(Buffer, ";", "|") 'replace ; with | Loop Close import 'import text file End Sub 'import text file with tabulator | Sub import() With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;" & ThisWorkbook.Path & "\test.txt", Destination:=Range("$A$1")) .Name = "test" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 1252 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileOtherDelimiter = "|" .TextFileColumnDataTypes = Array(1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With End Sub

The import macro was created with the macro recorder, as our friend pijaku suggested, whom I greet in passing

This code requires that both the csv and txt files be in the same folder as the workbook containing the macro

@+ The Woodpecker

mamined Posted messages 37 Status Member > cs_Le Pivert Posted messages 8437 Status Contributor

Hello
Thank you for the tip. The problem is that I can't replace the ';' with anything else because in my file it's not a separator but an integral part of a piece of data. Namely, the hours which are as follows: 07:05;32 to separate minutes and seconds.
So, if I do it manually, if I open the csv file with notepad for example, then select all and copy. Then I open an Excel workbook and paste, it's fine.

My issue in this specific case is that my macro above opens Explorer to allow me to select the file from which I want to copy everything and paste it into my tool.

So the need would be for it to do the same thing but by opening the csv with a text editor to solve the problem.
0

cs_Le Pivert Posted messages 8437 Status Contributor 730 > mamined Posted messages 37 Status Member

I suggest you put your CSV in a text file and open that same text file:

Sub AutoImportDataTXT() 'variable declaration Dim ListFile As Variant Dim Chemin_Fichier_CSV As String Dim Chemin_Fichier_TXT As String Dim MonApplication As Object Dim Buffer 'filter to only see the file type to select ListFile = Application.GetOpenFilename(Title:="Select data and import data", fileFilter:="CSV Files(*.csv*),*xls*", ButtonText:="Click") 'consider the case of cancel button click in case of 'error If ListFile <> False Then Chemin_Fichier_CSV = ListFile Chemin_Fichier_TXT = Replace(ListFile, "csv", "txt") Close Open Chemin_Fichier_CSV For Input As #1 Open Chemin_Fichier_TXT For Output As #2 Do While Not EOF(1) Line Input #1, Buffer Print #2, Buffer Loop Close End If Set MonApplication = CreateObject("Shell.Application") MonApplication.Open (Chemin_Fichier_TXT) Set MonApplication = Nothing End Sub

You just have to copy and paste

There you go,
@+ The Woodpecker

mamined Posted messages 37 Status Member > cs_Le Pivert Posted messages 8437 Status Contributor

A big thank you, Le Pivert,

it's magical and I'm able to do what I need to :-)
0

Answer 3 / 3
pijaku Posted messages 13513 Registration date Status Moderator Last intervention 2 773

Hello,

With your initial code, if you process the data once imported using Data/Convert, do you achieve the expected result?
If so, the macro recorder will save you...

--
Best regards,
Franck
0

Function Application.GetOpenFilename

3 answers

I can’t find the pc sound effects manager in windows 10

Honor magic 4 lite 5g asking me for a code to unlock

Dell pc slow at startup

Color cells by date

Green video

Software choice data recovery

New hard drive problem

Windows reinstall on formatted hard drive

Camera interference

Frying speaker squeal / crackling speaker