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.
 
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

M
  • Question Question
Data Transfer from Excel to PowerPoint and getting PDF file of PPT
Replies
1
Views
351
Sequoyah
Sequoyah
B
  • Question Question
VBA to delete attachment created on desktop
Replies
4
Views
107
bh24524
B
C
  • Question Question
VBA code for new workbook extraction with data grouping
Replies
0
Views
58
coding123456
C
M
  • Question Question
LDAP Issue Using Excel VBA
Replies
2
Views
68
malibuman67
M
H
  • Question Question
VBA is centering the image instead of aligning it to the left as usual in New Email
Replies
1
Views
288
Micron
M

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