Query

[Jaiguns63]

Hi, I need an assistance on VBA coding for my scenario. Though i am new to VBA learning, still i need some input from you.

Scenario: I have data from Column "A" to "I" (lets assume 20 rows)and based on that i am doing vlookup in column J Jand i have the data (only one cell - "J1"). Now i want to copy this "J1" formula cell and paste in in J columns until to last row of column I.

The range of number of rows with data is not the same on daily basis as it may increase or decrease. Hence depending on this can you tell me VBA code????

 

[FormR]

Hi, welcome to the forum!

You could try:

Range("J1").AutoFill Destination:=Range("J1:J" & Range("I" & Rows.Count).End(xlUp).Row)

But if you are programmatically entering the formula in J1 - then you could do it all in one step with something like, for example:

Range("J1:J" & Range("I" & Rows.Count).End(xlUp).Row).Formula = "=vlookup(a1,b:c,2,0)"

 

[Jaiguns63]

Thanks. I need additional favor from you PLZZZZZ.....

For vlookup up i have given a predefined path were my file is located. But next month I will create another new folder and place a new file. so next month when i run the macro, it will consider my previous old predefined file which should not be the case.

Hence when i run the macro every time for vlookup up, i need to call a window for selecting the required file and from there the vlookup action should take place.
Can you please help to write a code for my case? Appreciate your immediate response.

Thanks.

 

[FormR]

Hi, can you post an example of your VLOOKUP() formula and highlight which part you want to change based on the user selection? Can you also post the code you are using to place the formula into the worksheet?

 

[Jaiguns63]

Hi, can you post an example of your VLOOKUP() formula and highlight which part you want to change based on the user selection? Can you also post the code you are using to place the formula into the worksheet?

I recorded the macro and this is the code which has captured. Here you can see that vlookup source file is located in path\\ssci\F&A\02. O2C\01. AR\03. SEA\06. SG\X payment\DIVA REPORT\Invoicing\2019\April 2019\Creport Tracker-Apr-1.xlsb


My macro sheet is in my desktop. So by this coding i am doing a vlook up with my desktop file and with the above path file (Creport Tracker-Apr-1.xlsb). Instead of having the dedicated path, system should ask me to select the path and file for doing a vlookup with my desktop file.


Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=\\ssci\F&A\02. O2C\01. AR\03. SEA\06. SG\X payment\DIVA " _
, _
"REPORT\Invoicing\2019\April 2019\Creport Tracker-Apr-1.xlsb;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:Syste" _
, _
"m database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode" _
, _
"=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create " _
, _
"System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Re" _
, _
"plica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB" _
, ":Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False"), _
Destination:=Range("$A$1")).QueryTable

 

[Jaiguns63]

Any updates!!!!

 

[FormR]

Instead of having the dedicated path, system should ask me to select the path and file for doing a vlookup with my desktop file.

Hi, I don't see any VLOOKUP()'s in that code, but if I've understood correctly you could try this:

Dim MyFile As String


With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Filters.Add "Excel Files", "*.xls; *.xlsm; *.xls; *.xlsb", 1
    .Show
    If .SelectedItems.Count = 1 Then
      MyFile = .SelectedItems.Item(1)
    End If
End With


If Len(MyFile) = 0 Then
    MsgBox "No file chosen!"
    Exit Sub
End If


Application.CutCopyMode = False
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array("OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";" _
    , "User ID=Admin;Data Source=" & MyFile & ";Mode=Share Deny Write;Extended Properties=""HDR=YES;"";" _
    , "Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";" _
    , "Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;" _
    , "Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;" _
    , "Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:" _
    , "Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;" _
    , "Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;" _
    , "Jet OLEDB:Bypass ChoiceField Validation=False"), Destination:=Range("$A$1")).QueryTable
End With

Any updates!!!!

Try to remember that everyone here is a volunteer and not necessarily able to help at the drop of a hat - a little patience is sometimes needed.

 

[Jaiguns63]

Hi, The query works but the data is not copied to the macro sheet. I have given the complete code for your reference along with your code.

Sub Macro1()


With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Add "Excel Files", "*.xls; *.xlsm; *.xls; *.xlsb", 1
.Show
If .SelectedItems.Count = 1 Then
MyFile = .SelectedItems.Item(1)
End If
End With




If Len(MyFile) = 0 Then
MsgBox "No file chosen!"
Exit Sub
End If




Application.CutCopyMode = False
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array("OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";" _
, "User ID=Admin;Data Source=" & MyFile & ";Mode=Share Deny Write;Extended Properties=""HDR=YES;"";" _
, "Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";" _
, "Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;" _
, "Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;" _
, "Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:" _
, "Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;" _
, "Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;" _
, "Jet OLEDB:Bypass ChoiceField Validation=False"), Destination:=Range("$A$1")).QueryTable


.CommandType = xlCmdTable
.CommandText = Array("Sheet1$_FilterDatabase")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True

' ==========================================================================================================
' May be you need to check the below code from here which I think has an problem. The upper selected file should be copied and pasted in the new worksheet in macro file.
' ==========================================================================================================


.SourceDataFile = _
"\\ssci\F&A\02. O2C\01. AR\03. SEA\06. SG\X payment\DIVA REPORT\Invoicing\2019\April 2019\Creport Tracker-Apr-1.xlsb"
.ListObject.DisplayName = "Table_Creport_Tracker_Apr_1"
.Refresh BackgroundQuery:=False
End With
ActiveSheet.Name = "Tracker"

 

[FormR]

Hi, this has moved away quite a lot from the original question. In the future, if you have a new question make sure to start a new thread.

You could try adding these lines after your .PreserveColumnInfo = True line of code.

.SourceDataFile = myfile
.Refresh BackgroundQuery:=False

 

[Jaiguns63]

Dear Expert,
I nee an help from you. I need a VBA code from you for the below scenario.
Currently I am doing it manually and i want to automate it.

Current Scenario: I am from invoicing Team and i Generate multiple invoices for the day. I store all these Invoices in PDF mode and save it under my local drive folder or in a desktop folder. Like wise I generate invoice, save pdf invoice copies under local drive for all invoices. Assuming I generate and save 100 pdf copies and currently I send all these 100 pdf invoice copies to 100 recipients/mail address with new 100 emails individually/separately. It is time consuming for me to send all these 100 emails one by one to each different mail address.

Automation Required: I want to avoid this manual work and to automize the same. My idea is I want to run a macro which will send all pdf copies to required email address using outlook.

For this Once invoice copies printed and saved in local drive, I will maintain a excel database with invoice number, email address id, Signature as "Invoicing Team" columns with details entered in to it. Using this excel details, macro should run for each invoice numbers like open a black outlook email, entered the subject which is same off attachment of that particular pdf invoice copy, body of the email should be "invoice copy" and signature should be a default one as "invoicing Team".

Please help me with a VBA code for applying above scenarios.
Your help is much appreciated.


Thanks
Jay