VBA Code-csv file into excel- from general to text
I have a “.csv” file. Now we would like to copy data from this file into another Excel file. Doing this with my simple macro, I encounter a problem in some columns as I have numbers which are stored there in the format: “0000” and “01234567890”. Pasting the data makes then a “0” or “E09” out of the former source.
If I did that manually, I would do the following:
1.Copy from .csv file
2.Paste > “use text import wizard” > changing the respective fields (LFNR_... and EXTERNAL_ID) from “general” to “text”.
3.Would be happy – everything looks fine
This is our current macro:
ImportFileboxData Macro
'
Dim dateiname As String
dateiname = ThisWorkbook.Worksheets("MAS").Range("E7").Value
'
ThisWorkbook.FollowHyperlink ThisWorkbook.Worksheets("MAS").Range("E8").Value
Range("A2:AL10000").Select
Application.CutCopyMode = False
Selection.Copy
Windows("CreateReports.xlsm").Activate
Sheets("RawData").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows(dateiname).Activate
ActiveWorkbook.Close (True)
Windows("CreateReports.xlsm").Activate
Sheets("MAS").Select |
ß The hyperlink contains the address to where the file is stored
ß Here the selection of the data from the .csv starts
ß Here the ‘ordinary’ paste is taking place
ß into the spreadsheet where the macro button was hitted. |
Do you know a trick how to paste csv-data into an excel spreadsheet keeping the data okay?
