Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

import csv into another file excluding 3 columns

0

Hello, 

I' am trying to import data from a csv file and update an existing table in an excel sheet.

A)

When i run the vba code it doesn't sometimes exclude the colums and puts in the whole imported csv file

B) 

I always want to start the search file to start in "Downloads" however that works not correct

2 files attached to this request (csv file and workfile with vba code the "gegevens_ophalen" gets the import data)

Please help me to correct the code

excelmacros-vba
MS Office Version: 2016
posted by: ovdlaan on Wed Jul 14, 2021 at 10:16 am
Answer
Discuss

Answers

0

Otto

(You can't upload .csv files here, sorry, only Excel files).

On point (B), I assume you are referring to VA module csv_gegevens and the sub below. The changes in bold (the latter will replace some of yours) should always offer your download folder (in italics) but force the user to pick a file or press Cancel. If a file is selected, it will open that .xls* or csv file:

Sub gegevens_ophalen()

Dim fDialog As FileDialog, result As Integer
Dim r As String

Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

'Optional: FileDialog properties
fDialog.AllowMultiSelect = False
fDialog.Title = "Selecteer een bestand"
fDialog.InitialFileName = "D:\Users\Otto van der Laan\Downloads\"
'Optional: Add filters
fDialog.Filters.Clear
fDialog.Filters.Add "Excel files", "*.csv"

If fDialog.Show = True Then r = fDialog.SelectedItems(1)
If r<>"" then Workbooks.Open Filename:=r
Hope this helps. I don't have time to understand all your code/ comments in Dutch and) to answer your question (A).

Hope this helps,

posted by: John_Ru (rep: 6142) on Thu Jul 15, 2021 at 6:45 am
Discuss

Discussion

Thanks for your solution, however the start file is stll not the Download folder 
ovdlaan Jul 18, '21 at 8:55 am
Otto

Sorrry it doesn't help. I assume that the named drive exists e.g. if you paste D:\Users\Otto van der Laan\Downloads\ into the address bar of Windows File Explorer, it opens the Downloads folder. Not sure what to suggest (since the solution works for my Dwnloads folder on  my C: drive).
John_Ru (rep: 6142) Jul 18, '21 at 9:24 am
Thanks it is fixed now.
However in the same macro i tried to close the 2nd workbook without saving thru the macro but it seems not to hold the string of that second workbook how can i close the not active workbook ?
ovdlaan Jul 19, '21 at 7:57 am
Otto

Not quite sure which is the second workbook but if it's the one you open using the filepicker in Sub gegevens_ophalen(), I suggest you add the line
ActiveWorkbook.Close SaveChanges:=False
after the line Selection.Copy. 

Hope this does the trick for you.
John_Ru (rep: 6142) Jul 19, '21 at 8:21 am
Hello John 

There are 2 workbooks open the active one and the import csv file which has always a random filename. The only way i can use upto now is close both workbooks the active one is saved before the import file wil be closed without saving
ovdlaan Jul 20, '21 at 1:04 pm
Otto

Didn't that suggestion work? It should since the "import csv file" becomes the active workbook when you open it. Once it has closed, the main workbook becomes active. (Do you know you can use F8 in VB Explorer to step through your macro one line at a time?)
John_Ru (rep: 6142) Jul 20, '21 at 1:15 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login