Losing leading 0's when reading .csv

[mystery_man]

So im writing Macros code that reads in different workbooks, prompts users to make a decision and saves a results files. The problem is that when Leading 0's are eliminated for numerical numbers with less than 8 digits. At the moment im reading in the files by:

Set VATWkbk = Workbooks.Open(varPath & pathSybol & "VAT.csv"
VATWkbk.Worksheets(1).USedRange.Copy Destination :=MAINWkbk.Worksheets("VAT").Range ("A1")
VATWkbk.Close SaveChanges:=False

I tried.OpenText but i kept on getting errors in trying to get it to work. (Im very new to VBA). Any help on this would be great thanks

 

[Joe4]

Welcome to the Board!

The issue is that when opening CSV files directly in Excel, Excel makes it own decisions on the format of fields, and formatting numbers with leading zeroes as numbers (and not text) will cause those leading zeroes to drop.

There are a few ways around this, such as:
- Change the file extension from "csv" to something like "txt", which should invoke the Import Wizard and allow you to specify the format of the field as Text
- Import the file by going to Data -> Get External Date -> From Text. This will also invoke the Import Wizard.

Note that you can get VBA code for either of these methods my turning on the Macro Recorder and recording yourself manually opening/importing the files.