Ted Murphy
New member
Not sure if this is a PQ or a vba problem … possible somewhere in the middle. But it is causing much grief.
Quick synopsis of problem … It is to do with European (and Canadian) dates getting converted to US format.
Step 1:
N.B. Interestingly, if I do a Save As from here and save a .txt (Tab Delimited) file then everything works ok. i.e the date retains it European format
Step 2:
I want to save the data as a .txt (Tab Delimited) file in a specific directory.
As the system will be used by a non-technical user, I have put the creation of the .txt (Tab Delimited) into a vba macro. (My vba skill level is 1- on a scale from 1 to 10!!)
So I have cobbled this together as best as I could.
The worksheet is copied to another book before saving … to avoid confusion for the operator.
This is an extract from the vba macro:
Step 3:
Import the .txt (Tab Delimited) file using Power Query for subsequent loading to PowerPivot
… and the Date has been reversed … causing my Load to fail.
Any ideas as to where the problem may lie?
Quick synopsis of problem … It is to do with European (and Canadian) dates getting converted to US format.
Step 1:
I pull data into a Worksheet using Power Query / ODBC
Append the current Month end date … 31/05/2016
Only 3 columns in the table
Append the current Month end date … 31/05/2016
Only 3 columns in the table
Item Code | Date | Std Cost |
1008 | 31/05/2016 | 272.231 |
1018 | 31/05/2016 | 204.828 |
1023 | 31/05/2016 | 191.3 |
N.B. Interestingly, if I do a Save As from here and save a .txt (Tab Delimited) file then everything works ok. i.e the date retains it European format
Step 2:
I want to save the data as a .txt (Tab Delimited) file in a specific directory.
As the system will be used by a non-technical user, I have put the creation of the .txt (Tab Delimited) into a vba macro. (My vba skill level is 1- on a scale from 1 to 10!!)
So I have cobbled this together as best as I could.
The worksheet is copied to another book before saving … to avoid confusion for the operator.
This is an extract from the vba macro:
' Save Standard Costs
With ThisWorkbook
' The path and name of the Output File
TxtFileName = .Worksheets("Parameters").Range("cost_prices_folder") & "CY_" & _
.Worksheets("Parameters").Range("calendar_year") & "_" & _
.Worksheets("Parameters").Range("calendar_month") & " Cost Prices.txt"
Set TxtWorkbook = Workbooks.Add ' Create a new Workbook to hold the Tab Delimited Output Worksheet
' Copy the Data Worksheet to the new Tab Delimited Output Workbook
.Worksheets("Standard Costs").Copy TxtWorkbook.Sheets(Sheets.Count)
Application.DisplayAlerts = False ' Suppress the Overwrite Prompt when the Output File already exists
' Save the new Tab Delimited File (Workbook) in XlText (Tab Delimited) Format
TxtWorkbook.SaveAs _
Filename:=TxtFileName, _
FileFormat:=xlText, _
CreateBackup:=False
TxtWorkbook.Close 'SaveChanges = False
Application.DisplayAlerts = True
End With
With ThisWorkbook
' The path and name of the Output File
TxtFileName = .Worksheets("Parameters").Range("cost_prices_folder") & "CY_" & _
.Worksheets("Parameters").Range("calendar_year") & "_" & _
.Worksheets("Parameters").Range("calendar_month") & " Cost Prices.txt"
Set TxtWorkbook = Workbooks.Add ' Create a new Workbook to hold the Tab Delimited Output Worksheet
' Copy the Data Worksheet to the new Tab Delimited Output Workbook
.Worksheets("Standard Costs").Copy TxtWorkbook.Sheets(Sheets.Count)
Application.DisplayAlerts = False ' Suppress the Overwrite Prompt when the Output File already exists
' Save the new Tab Delimited File (Workbook) in XlText (Tab Delimited) Format
TxtWorkbook.SaveAs _
Filename:=TxtFileName, _
FileFormat:=xlText, _
CreateBackup:=False
TxtWorkbook.Close 'SaveChanges = False
Application.DisplayAlerts = True
End With
Step 3:
Import the .txt (Tab Delimited) file using Power Query for subsequent loading to PowerPivot
… and the Date has been reversed … causing my Load to fail.
This is how the first 3 row appear if you open the file using Notepad:
Item Code Date Std Cost
1008 5/31/2016 272.231
1018 5/31/2016 204.828
1023 5/31/2016 191.3
Any ideas as to where the problem may lie?