Domroy

Board Regular
Joined
Mar 8, 2018
Messages
114
I have a query that's connected to a file that's supposed to automatically every day. Today, for some reason (not my issue), the file wasn't there, but my query didn't tell me the file was missing. Can I flag the query so that if the file is missing it alerts me?

Thank you!

Judi
 
OH! No, unfortunately I can't post this data. Hmm...any other way you can help? What info do you need?
 
Upvote 0

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
ok, it would be much easier with source but I can create dummy data for that. It will take more time only.
or
if it's possible maybe 3 rows from source of txt (headers and two data rows (you can change sensitive data to any other)
 
Last edited:
Upvote 0
Well, if you have the time, cool. All this does, is "Get and Transform Data" in a query. It pulls a text file into a table. It looks in a folder for the file, and if the file is not there, I want an error message. What other data can I give you?
 
Upvote 0
see 2nd part of post#12 (or even without data, only headers line)

all except data I see from M-code :)
 
Last edited:
Upvote 0
MCA
MCA_MISS
PROV_NAME
PROV_MISS
MRN
PATIENT
FIRSTVIS
LASTVIS
LASTVIS_TYPE
APPT_STAT
NEXT_APPT
ACCT_ID
DOI
EMPLOYER
PAYOR
MOD_
OFF_
SURGERYDT
SURGERYPROC
CLOSEDREASON
ARX_MMI
FACE_PS
DX_ARX
ICD10
CASE_REVIEW_DATE
CASE_FOLLOWUP_DATE
CLOSED_DATE
FUT_MED
LAST_OPN_DT


Those are the headers.
 
Upvote 0
ok, try this on copy of your original file

replace M-code in Advanced Editor with this below:

Code:
[SIZE=1]let
    TFR = Table.TransformColumnTypes(Table.FromRecords({[DateTime = DateTime.FixedLocalNow(),Type = "Error",Kind = "Source data is not available"]}),{{"DateTime", type datetime}}),
    Source = Csv.Document(File.Contents("O:\Dashboards\Disability Mgmnt\Data\DM_raw.txt"),[Delimiter="|", Columns=29, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = try Table.TransformColumnTypes(#"Promoted Headers",{{"MCA", type text}, {"MCA_MISS", type text}, {"PROV_NAME", type text}, {"PROV_MISS", type text}, {"MRN", type number}, {"PATIENT", type text}, {"FIRSTVIS", type date}, {"LASTVIS", type date}, {"LASTVIS_TYPE", type text}, {"APPT_STAT", type text}, {"NEXT_APPT", type datetime}, {"ACCT_ID", type number}, {"DOI", type date}, {"EMPLOYER", type text}, {"PAYOR", type text}, {"MOD_", Int64.Type}, {"OFF_", Int64.Type}, {"SURGERYDT", type datetime}, {"SURGERYPROC", type text}, {"CLOSEDREASON", type text}, {"ARX_MMI", type date}, {"FACE_PS", type date}, {"DX_ARX", type text}, {"ICD10", type text}, {"CASE_REVIEW_DATE", type date}, {"CASE_FOLLOWUP_DATE", type date}, {"CLOSED_DATE", type date}, {"FUT_MED", type text}, {"LAST_OPN_DT", type datetime}}) otherwise TFR
in
    #"Changed Type"[/SIZE]

it will give you a small info table instead of table of your txt file.

DateTimeTypeKind
14/06/2019 19:54​
ErrorSource data is not available

if txt file will be available then you should see the data from there (after refresh of course :) )
Everything after refresh
 
Last edited:
Upvote 0
one more thing

honestly, you don't need vba button
refresh time interval you can define here:

refreshinterval.jpg


or if you want suddenly refresh use Ctrl+Alt+F5 or Refresh All from the ribbon
 
Upvote 0
Also, I'll let you know how the code is working once I can get back into the document and save a test file. Right now, it's crashing my work computer.

But with the connection refresh, it'll do that even if the document is closed?
 
Upvote 0
set Refresh data when opening the file
and you'll get fresh data if you open the file in the morning or whenever you open it

which document? txt or excel file?

Right now, it's crashing my work computer.

I hope it's not my fault
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top