New and Seeking VBA Help

T

TheOpportunist

New Member
Joined
Apr 10, 2014
Messages
3
Hey guys (and gals)!

I'll just start off by introducing myself, my name's Rob, I am new to programing (been learning off and on for the last 3 months) and and very new to Visual Basics (as in as of yesterday).

I took it upon myself to take a project up at work and I'm feeling a little over my head. I am here to seek any assistance that can be offered.

So here is the objective. I have a weekly file provided to me (excel file), I've been asked to create a script in Access that will allow staff to click a button, select the excel file, and have it export a new excel file after applying a bunch of filters. Right now I am just working on getting one of those filters to work: if "--" appears in Range "J" (column), select specific columns for that row (account) and include it in the exported excel file. I have... something... so far but it's not exactly working.

(once I get help with this I feel confident I can figure out how to get the rest of the filters to work).

Here is what I have so far:

Code: 
[COLOR=#000000][FONT=Calibri]Private Sub cmdBrowser_Click()[/FONT][/COLOR][COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]   Dim fDialog As Office.FileDialog[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]   Dim varFile As Variant[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]   Dim fso, myFile[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]   Dim linefolloup As String[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]   Dim lineCount As Integer[/SIZE][/FONT][/FONT][/COLOR]


[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]   Dim db As DAO.Database[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]   Dim rs As DAO.Recordset[/SIZE][/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Segoe UI][FONT=Calibri]   Dim i As Integer[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]   Dim iNumCols As Integer, iNumRows As Integer
[/SIZE][/FONT][FONT=Calibri]' [/FONT][FONT=Calibri]Dim c As Object[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI]
[/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]   Dim oApp As Object[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]   Dim oBook As Object[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]   Dim oSheet As Object[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI]
[/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]   Set fso = CreateObject("Scripting.FileSystemObject")[/SIZE][/FONT][/FONT][/COLOR]


[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]    Set oApp = CreateObject("Excel.Application")
[/SIZE][/FONT][FONT=Calibri]    ' Set c = CreateObject("Range")[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]    Set oBook = oApp.Workbooks.Add[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]    Set oSheet = oApp.WorkSheets(1)[/SIZE][/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]   With fDialog[/SIZE][/FONT][/FONT][/COLOR]


[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]      .AllowMultiSelect = False[/SIZE][/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]      .Title = "Please select the file to import"[/SIZE][/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]      .Filters.Clear[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]      .Filters.Add "Excel Files", "*.xlsx"[/SIZE][/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]      If .Show = True Then[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]          For Each varFile In .SelectedItems[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                Set myFile = fso.OpenTextFile(varFile, 1)[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                ' Me.txtFileName.Value = fso.GetFileName(varFile)[/SIZE][/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                Set oBook = oApp.Workbooks.Add[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                Set oSheet = oBook.WorkSheets(2)[/SIZE][/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                For i = 1 To 12[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                 If i = 1 Then[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                    oSheet.Cells(1, i).Value = "Account"[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                 ElseIf i = 2 Then[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                    oSheet.Cells(1, i).Value = "Corp"[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                 ElseIf i = 3 Then[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                    oSheet.Cells(1, i).Value = "First Name"[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                 ElseIf i = 4 Then[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                    oSheet.Cells(1, i).Value = "Last Name"[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                 ElseIf i = 5 Then[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                    oSheet.Cells(1, i).Value = "Account Status"[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                 ElseIf i = 6 Then[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                    oSheet.Cells(1, i).Value = "Credit Score"[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                 ElseIf i = 7 Then[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                    oSheet.Cells(1, i).Value = "Score Date"[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                 ElseIf i = 8 Then[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                    oSheet.Cells(1, i).Value = "EFTS"[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                 ElseIf i = 9 Then[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                    oSheet.Cells(1, i).Value = "NSF"[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                 ElseIf i = 10 Then[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                    oSheet.Cells(1, i).Value = "Returns"[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                 ElseIf i = 11 Then[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                    oSheet.Cells(1, i).Value = "Rental Eq"[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                 ElseIf i = 12 Then[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                    oSheet.Cells(1, i).Value = "Purchased Eq"[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                 End If[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                Next[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                 lineCount = 1[/SIZE][/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                    While myFile.AtEndOfStream = False[/SIZE][/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                        linefolloup = myFile.ReadLine[/SIZE][/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                        folloupCode1 = Mid(Range("J"), 1, 2)[/SIZE][/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Segoe UI][FONT=Calibri]         If lfolloupCode = "--" Then[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                            lineCount = lineCount + 1[/SIZE][/FONT][/FONT][/COLOR]


[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                            accountNumber = Mid(B1, 1, 11)[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                            oSheet.Cells(lineCount, 1).Value = accountNumber

[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                            corpNumber = Mid(C1, 1, 3)[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                            oSheet.Cells(lineCount, 2).Value = corpNumber

[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                            firstName = Mid(F1, 1, 20)[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                            oSheet.Cells(lineCount, 3).Value = firstName

[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                            lastName = Mid(G1, 1, 20)[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                            oSheet.Cells(lineCount, 4).Value = lastName[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                               End If

[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                        '   CurrentDb.Execute strInsertfolloup[/SIZE][/FONT][/FONT][/COLOR]



[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                     Wend[/SIZE][/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                oSheet.SaveAs "C:\Users\****\Desktop\Working\Follow Up Export_" & Year(Date) & Month(Date) & Day(Date) & ".xls"[/SIZE][/FONT][/FONT][/COLOR]


[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                ' MsgBox strQuery[/SIZE][/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                ' CurrentDb.Execute strQuery[/SIZE][/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                 MsgBox "Export successful!", vbOKOnly, "Import"[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                ' End If[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]                myFile.Close[/SIZE][/FONT][/FONT][/COLOR]


[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]               '  Me.TxtNbOfRecord.Value = lineCount[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]          Next varFile[/SIZE][/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]      End If[/SIZE][/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]   End With[/SIZE][/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]   Set oSheet = Nothing[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]   Set oBook = Nothing[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Segoe UI][FONT=Calibri][SIZE=2]   Set oApp = Nothing[/SIZE][/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Calibri]End Sub
[/FONT][/COLOR]

In 24 hours I don't feel like I quiet have it down right, I don't get any compile errors but the out-put file only has the headings but the rest is blank. I get the feeling it may have something to do with not defining some sub/variables right for it to work from pulling an excel file (I think the way I have it is more for a text file)... I don't think I should be using MID but when I try to use Range I get a compile issue.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
I've scrapped what I had there and decided to re-do it all using record macro.. so now when I open the excel file I search for the macro, run it, and it spits out everything I want.


Is there a way I can take this data and make it run in Access instead and have you select the excel file you want this macro to run on?
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Using VBA to Flag and Calculate Overlapping Shift Times
Replies
0
Views
167
Snort
S
E
  • Question
Force Excel 2019 VBA to import multiple txt file to existing sheet & not seperate wk.book / wk.sheets
Replies
1
Views
108
Domenic
D
M
  • Question
Share excel file on Sharepoint using VBA
Replies
1
Views
323
mariyarashkova
M
A
  • Question
VBA Code for rotating TextBox
Replies
3
Views
233
rlv01
R
D
  • Solved
VBA Excel OpenDialogBox then Transfer the information to cell
Replies
5
Views
308
Daniza Morla
D

Forum statistics

Threads
1,216,577
Messages
6,131,513
Members
449,654
Latest member
andz

Share this page

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