Geek Answers Handbook

Excel VBA date formats / values ​​change when a file is opened programmatically

I have a problem opening the .csv file programmatically. A file is a temporary series of daily data with several data points associated with each date.

When I manually open the file, the dates open correctly as the date format dd/mm/yyyy. However, when I open the file programmatically, the dates until the 12th day of each month open as mm/dd/yyyy, although the format remains dd/mm/yyyy(for example, July 1, 1983 (1/7/1983), it will open as January 7, 1983 (7/1/1983) - it’s not just a formatting issue, Julian’s date (days from January 1, 1901) associated with these dates also changes), and the dates after the 12th of every month open correctly, although as text, not dates.

Data in the form of text is not a problem, but changing the date the file is opened is problematic. I could try to import the entire CSV file as comma-delimited text rather than opening the file, however it would be easier and faster if I could stop changing dates when opening the file.

Has anyone had a similar problem with this in the past? Any advice on this would be greatly appreciated. Hello Ben.

Flder = InputBox("Copy and Paste Folder path here:")

Set FSO = CreateObject("Scripting.FileSystemObject")
Set SourcePath = FSO.GetFolder(Flder)

For Each File In SourcePath.Files        
    Workbooks.Open (File)

    FlNm = File.Name

    StrtCol = Workbooks(FlNm).Worksheets(1).Range(Cells(4, 1), Cells(4, 30)).Find ("Mean").Column

    Workbooks(FlNm).Worksheets(1).Range(Cells(1, 1), Cells(60000, 1)).Copy (Workbooks("Find Water Years V2.xls").Worksheets(1).Range("A3"))
    Workbooks(FlNm).Worksheets(1).Range(Cells(1, StrtCol), Cells(60000, StrtCol + 1)).Copy (Workbooks("Find Water Years V2.xls").Worksheets(1).Range("B3"))

    Workbooks(FlNm).Close
Next

The problem occurs in the line Workbooks.Open (File). Sorry to not throw this away for starters.

+6
source share
6 answers

Since the question has already been answered by OP in the comments, but not published as an official answer, I will post it here if someone else skips it like me.

workbook = workbooks.Open(filename, Local:= true)

Local = true, , mdy, , (dmy), Open()

+9
  

CSV -, , , .

  

, CDT. .OpenText .Open. :)

, , .

Workbooks.OpenText Filename:= File, _
Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True
0

, 2981639 , "Local: true", "local: = True"

, 2 memo csv. - , memo , 255 , CRLF , .

, ,

0

. . .txt excel, , , dd-mm-yyyy mm-dd-yyyy. . (, Local: = True). . , .

Note that while trasferring .txt file to excel use File Origin as "xlWindows:
    Workbooks.OpenText Filename:= _
    ThisWorkbook.Worksheets("Reporting").Cells(3, 6), Origin:=xlWindows, _
    StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(49 _
    , 1), Array(50, 1), Array(67, 1), Array(80, 1), Array(93, 1), Array(106, 1), Array(119, 1)) _
    , DecimalSeparator:=",", ThousandsSeparator:=".", TrailingMinusNumbers:= _
    True, Local:=True

. - - .

0

. , .

. , . .

0

I ran into this issue in Office 365 . When importing a csv file into Excel, some dates dd / mm / jjjj (European) were imported as American dates mm / dd / jjjj. The csv file does not format private dates.

Opening as follows:

Set xla = CreateObject("Excel.Application")
Set xlb = xla.Workbooks.Open(sFilePath, ReadOnly:=False, Local:=True)

Local settings were used, and the problem is resolved :-)

Other options for opening a file:

expression.Open (FileName, UpdateLinks, ReadOnly, 
                 Format, Password, WriteResPassword, 
                 IgnoreReadOnlyRecommended, Origin, 
                 Delimiter, Editable, Notify, Converter, 
                 AddToMru, Local, CorruptLoad)
0
source

More articles:


All Articles