$$Excel-Macros$$ Help required for changing the file name in a query programmatically
Dear all, I am daily using the attached sample query file to extract data from an excel file stored in my windows desktop. I am pasting below a part of code from the test.dqy file, where I am daily changing the file name based on the date on which it is being run. Instead of changing it manually, is there any possibility to change it based on the system date. ie T2408 in place of T2308 if I run the query on 23/08/2014. SELECT `Sheet1$`.Date, `Sheet1$`.DptNo, `Sheet1$`.Empno, `Sheet1$`.Leave FROM `C:\Users\jeb501\Desktop\T2308.xls`.`Sheet1$` `Sheet1$` WHERE (`Sheet1$`.DptNo='01') Note : I am using EXCEL 2007 TIA Eugene -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. Test.dqy Description: Binary data
Re: $$Excel-Macros$$ Help required for changing the file name in a query programmatically
Hi, See this example extracts the data from your file T2308.xls (T & date: and change the name according to the current(or system date)), using ADO and SQL statement. regards. Basole 2014-08-23 8:55 GMT-03:00 Eugene Bernard : > Dear all, > > I am daily using the attached sample query file to extract data from an > excel file stored in my windows desktop. > > I am pasting below a part of code from the test.dqy file, where I am daily > changing the file name based on the date on which it is being run. > > Instead of changing it manually, is there any possibility to change it > based on the system date. > > ie T2408 in place of T2308 if I run the query on 23/08/2014. > > SELECT `Sheet1$`.Date, `Sheet1$`.DptNo, `Sheet1$`.Empno, `Sheet1$`.Leave > FROM `C:\Users\jeb501\Desktop\T2308.xls`.`Sheet1$` `Sheet1$` WHERE > (`Sheet1$`.DptNo='01') > Note : I am using EXCEL 2007 > > TIA > > Eugene > > -- > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s > =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ > https://www.facebook.com/discussexcel > > FORUM RULES > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > 2) Don't post a question in the thread of another member. > 3) Don't post questions regarding breaking or bypassing any security > measure. > 4) Acknowledge the responses you receive, good or bad. > 5) Jobs posting is not allowed. > 6) Sharing copyrighted material and their links is not allowed. > > NOTE : Don't ever post confidential data in a workbook. Forum owners and > members are not responsible for any loss. > --- > You received this message because you are subscribed to the Google Groups > "MS EXCEL AND VBA MACROS" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to excel-macros+unsubscr...@googlegroups.com. > To post to this group, send email to excel-macros@googlegroups.com. > Visit this group at http://groups.google.com/group/excel-macros. > For more options, visit https://groups.google.com/d/optout. > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. GetTdateExample.xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
Re: Fwd: $$Excel-Macros$$ Doubt MBA
Tks in advance for the help. Yes, I´m using Outlook and the extension of the file is PDF. I appreciate your help. Marcio. 2014-08-22 8:24 GMT-03:00 Paul Schreiner : > what email program are you using? (Outlook?) > Is "File_Name" and "File_Name2" the full names? or is there also a file > extension? > > (so, in your sheet, you have "File_Name" but the actual filename is: > "File_Name.docx"?) > > *Paul* > - > > > > > > > > *“Do all the good you can,By all the means you can,In all the ways you > can,In all the places you can,At all the times you can,To all the people > you can,As long as ever you can.” - John Wesley* > - > >*From:* Marcio Bandeira > *To:* excel-macros@googlegroups.com > *Sent:* Thursday, August 21, 2014 9:04 PM > *Subject:* Fwd: $$Excel-Macros$$ Doubt MBA > > i forgot something very importante. I need a extra collum to say if I send > the file to this cliente or not. New example. Tks!!! > > > > Hi. I need a help. I have a worksheet and I need to build a macro that > send a file, with the name is in collum B to the e-mails at collum c. THere > is a Excel attached to explain. I apreciate any help. > > Thank you very much > > > Marcio. > -- > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s > =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ > https://www.facebook.com/discussexcel > > FORUM RULES > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > 2) Don't post a question in the thread of another member. > 3) Don't post questions regarding breaking or bypassing any security > measure. > 4) Acknowledge the responses you receive, good or bad. > 5) Jobs posting is not allowed. > 6) Sharing copyrighted material and their links is not allowed. > > NOTE : Don't ever post confidential data in a workbook. Forum owners and > members are not responsible for any loss. > --- > You received this message because you are subscribed to the Google Groups > "MS EXCEL AND VBA MACROS" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to excel-macros+unsubscr...@googlegroups.com. > To post to this group, send email to excel-macros@googlegroups.com. > Visit this group at http://groups.google.com/group/excel-macros. > For more options, visit https://groups.google.com/d/optout. > > >-- > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s > =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ > https://www.facebook.com/discussexcel > > FORUM RULES > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > 2) Don't post a question in the thread of another member. > 3) Don't post questions regarding breaking or bypassing any security > measure. > 4) Acknowledge the responses you receive, good or bad. > 5) Jobs posting is not allowed. > 6) Sharing copyrighted material and their links is not allowed. > > NOTE : Don't ever post confidential data in a workbook. Forum owners and > members are not responsible for any loss. > --- > You received this message because you are subscribed to the Google Groups > "MS EXCEL AND VBA MACROS" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to excel-macros+unsubscr...@googlegroups.com. > To post to this group, send email to excel-macros@googlegroups.com. > Visit this group at http://groups.google.com/group/excel-macros. > For more options, visit https://groups.google.com/d/optout. > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For
$$Excel-Macros$$ Keeping two blank cell & that is merged after the text "Expire Date:"
Dear all, I am having data like below in the cell A & B in the sheet input
cell A cell B
Tra Code:RNSPIN
News:what ever it is
PDate: 8/5/2013
Expire Date: 8/12/2013
Tra Code:BD NEW
News:what ever it is
PDate: 8/5/2013
Expire Date: 8/12/2013
Tra Code:CS NEW
News:what ever it is
PDate: 8/5/2013
Expire Date: 8/12/2013
In the sheet output , Now I want to keep two blank cell & that is
merged after the text “Expire Date:” onwards
Tra Code: RNSPIN
News: what ever it is
Pdate: 8/5/2013
Expire Date:8/12/2013
Tra Code: BD NEW
News: what ever it is
Pdate: 8/5/2013
Expire Date:8/12/2013
Tra Code: CS NEW
News: what ever it is
Pdate: 8/5/2013
Expire Date:8/12/2013
here is an example code that works for two blank cell
Sub fff()
'by lancer102rus
With ActiveSheet
lrow& = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = lrow - 1 To 1 Step -1
If .Cells(i, "A") = "Expire Date:" Then
.Cells(i + 1, "A").EntireRow.Insert
.Cells(i + 1, "A").EntireRow.Insert
'Range("A5:B6").Merge ' here i assumed that cell
A5:B6 was being blank. so it will be merge
'Range("A11:B12").Merge ' as per logic next blank
cell is A11:B12" so cell A11:B12 will be merge
'
'
' like wise till last row
End If
Next i
End With
End Sub
please check the attatchment
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
2 blank cell _merge.xlsm
Description: application/vnd.ms-excel.sheet.macroenabled.12
$$Excel-Macros$$ Create macro to save macro template as macro enabled workbook using cell value as file name
*Create macro to save macro template as macro enabled workbook using cell
value as file name*
This is what I'm trying to do. Save the Macro Template on a network drive
as a Macro Excel file using a cell value as the file name.
I got this working but now when I try to open the file it saved the error
message is: file is corrupt or format not recognized.
What up wid dat ?
Sub Save_Me()
'
' Save Me Macro
'
'
ActiveWorkbook.SaveCopyAs Filename:="F:\GP SAVED BY RICH\" &
Range("E5").Value & ".xlsm"
End Sub
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Keeping two blank cell & that is merged after the text "Expire Date:"
Dear All , My previous attached file was wrong. here is the update file. On 8/23/14, Nur Hossain https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. blank cell _merge_new.xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
Re: $$Excel-Macros$$ Keeping two blank cell & that is merged after the text "Expire Date:"
Hi
Check this...
Cheers!!
On Sun, Aug 24, 2014 at 8:12 AM, Nur Hossain wrote:
> Dear All , My previous attached file was wrong. here is the update file.
>
>
>
> On 8/23/14, Nur Hossain Dear all, I am having data like below in the cell A & B in the sheet input
> cell A cell B
> Tra Code: RNSPIN
> News: what ever it is
> PDate: 8/5/2013
> Expire Date: 8/12/2013
> Tra Code: BD NEW
> News: what ever it is
> PDate: 8/5/2013
> Expire Date: 8/12/2013
> Tra Code: CS NEW
> News: what ever it is
> PDate: 8/5/2013
> Expire Date: 8/12/2013
>
> In the sheet output , Now I want to keep two blank cell & that is
> merged after the text “Expire Date:” onwards
>
> Tra Code: RNSPIN
> News: what ever it is
> Pdate: 8/5/2013
> Expire Date: 8/12/2013
>
>
> Tra Code: BD NEW
> News: what ever it is
> Pdate: 8/5/2013
> Expire Date: 8/12/2013
>
>
> Tra Code: CS NEW
> News: what ever it is
> Pdate: 8/5/2013
> Expire Date: 8/12/2013
>
> here is an example code that works for two blank cell
>
> Sub fff()
> 'by lancer102rus
> With ActiveSheet
> lrow& = .Cells(.Rows.Count, "A").End(xlUp).Row
>
> For i = lrow - 1 To 1 Step -1
> If .Cells(i, "A") = "Expire Date:" Then
> .Cells(i + 1, "A").EntireRow.Insert
> .Cells(i + 1, "A").EntireRow.Insert
> 'Range("A5:B6").Merge ' here i assumed that cell
> A5:B6 was being blank. so it will be merge
> 'Range("A11:B12").Merge ' as per logic next blank
> cell is A11:B12" so cell A11:B12 will be merge
> '
> '
> ' like wise till last row
>
> End If
> Next i
> End With
> End Sub
>
> please check the attachment
>
> --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegroups.com.
> Visit this group at http://groups.google.com/group/excel-macros.
> For more options, visit https://groups.google.com/d/optout.
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
blank cell _merge_new.xlsm
Description: application/vnd.ms-excel.sheet.macroenabled.12
